Scalar Subqueries — Use scalar subqueries in SELECT (intro)

The relational model, proposed by E.F. Codd in 1970, introduced the idea that data could be queried declaratively rather than navigated procedurally. Early implementations of SQL—then called SEQUEL—provided basic retrieval capabilities, but the language quickly needed composability: the ability to nest one query inside another. Subqueries emerged as the primary mechanism for this composition, allowing a query to reference the result of another query as though it were a value, a table, or a condition. Among the various forms of subqueries, the scalar subquery—a subquery guaranteed to return exactly one row and one column—became especially useful for embedding computed values directly into the SELECT list. This capability addressed a fundamental question: how can you augment each row of a result set with a value that depends on a separate aggregation or lookup, without resorting to temporary tables or application-level code?

The central question this lesson addresses is straightforward yet powerful: how do you place a complete, self-contained query inside the SELECT clause so that every row in your result set receives a dynamically computed scalar value? Understanding this technique unlocks an elegant pattern for column-level enrichment without the overhead of explicit JOINs or multi-step procedural logic.

A scalar subquery is defined by a single constraint: it must return exactly one row and one column. When placed inside the SELECT list, the database engine evaluates the subquery for each row produced by the outer query (conceptually, at least—the optimizer may rewrite the execution plan). If the subquery returns zero rows, the result is NULL; if it returns more than one row, the engine raises a runtime error. This guarantee of a single value is what makes the subquery "scalar"—analogous to a scalar quantity in mathematics, which has magnitude but no additional dimensionality.

Notice how the correlation variable e.dept_id in the subquery's WHERE clause creates a dependency on the outer row. For each employee, the engine conceptually re-evaluates the inner AVG over only those employees sharing the same department. The result is that Alice and Carol—both in department 3—receive the same avg_dept_salary of 72500.00, while Bob, in department 1, receives a different average. This is precisely the kind of row-specific enrichment that makes scalar subqueries in SELECT so versatile: each row can carry a derived value that would otherwise require a JOIN to a grouped aggregate or a window function.

General Syntax Pattern

Logical Evaluation Order

Although SQL is declarative and the physical execution plan varies by engine, the logical evaluation order remains consistent. The FROM clause establishes the source rows. The WHERE clause filters them. Only after filtering does the SELECT list evaluate its expressions—including any scalar subqueries. This means a scalar subquery in SELECT cannot filter the outer rows; it merely decorates them. If you want filtering behavior based on a subquery, the subquery belongs in the WHERE clause. Understanding this distinction prevents a common design error where developers attempt row filtering via a SELECT-level subquery and wonder why all rows still appear.

Runtime Error Conditions

The scalar contract is strict. If your subquery ever returns more than one row for any given evaluation, the database will raise an error such as PostgreSQL's ERROR: more than one row returned by a subquery used as an expression or SQL Server's Msg 512: Subquery returned more than 1 value. Aggregate functions without GROUP BY are the most reliable way to guarantee a single-row result. Alternatively, adding LIMIT 1 can enforce scalar behavior, though this may mask data issues and should be used judiciously.

Scalar subqueries in SELECT appear in several recurring patterns. The following diagram categorizes the most common use cases you will encounter in practice, from simple constant injection to per-row correlated lookups.

Patterns 1 and 3 are uncorrelated: the inner query does not reference any column from the outer query, so it can be evaluated once and its result cached. Patterns 2 and 4 are correlated: the inner query references a column from the outer query (e.g., o.cust_id or c.id), so the engine must logically re-evaluate the inner query for each outer row. The distinction matters for performance reasoning: uncorrelated subqueries are O(1) with respect to the outer row count, while correlated ones are conceptually O(N × M) where N is the outer row count and M is the inner scan cost per evaluation—though modern optimizers aggressively de-correlate these into hash joins.

Suppose we have an employees table with columns id, name, dept_id, and salary. We want to produce a report that shows each employee's name, salary, the average salary in their department, and how much they deviate from that average—all in a single query.

Scalar subqueries in SELECT represent the earliest composability mechanism in SQL. As the language evolved, two more powerful abstractions emerged that often serve the same purpose: window functions (introduced in SQL:2003) and Common Table Expressions (CTEs) (SQL:1999). Understanding the relationship between these three tools is essential for writing idiomatic, maintainable SQL.

As you progress beyond this introduction, you will find that many queries initially written with scalar subqueries can be refactored into window functions for better performance and clarity—particularly when you need multiple aggregates over the same partition. Similarly, CTEs allow you to define reusable named result sets that eliminate subquery duplication. Think of scalar subqueries as the foundational building block: once you understand how a single-value subquery fits into the SELECT list, the transition to window functions (AVG(salary) OVER (PARTITION BY dept_id)) and CTEs (WITH dept_avg AS (SELECT ...)) becomes a natural generalization of the same idea: composing queries from queries.