SQL Subqueries

A subquery is a query nested inside another SQL query, used to compute an intermediate result that the outer query then uses — a technique that allows expressing more complex logic than a single flat query could handle on its own.

Basic concept. A subquery is written inside parentheses and can appear in various parts of an outer query: in the WHERE clause to filter rows based on a computed condition, in the SELECT clause to compute a derived value, or in the FROM clause to treat the subquery's result as if it were a table. The database evaluates the subquery (in many cases) and then uses its result as part of evaluating the outer query.

Subqueries in `WHERE` clauses. One of the most common uses is filtering rows in an outer query based on a value or set of values computed by a subquery — for example, finding all orders placed by customers who live in a specific city, where the list of matching customer IDs is computed by a subquery against the customers table, and the outer query then filters orders against that computed list.

Scalar subqueries. A scalar subquery returns exactly one value (one row, one column), and can be used anywhere a single value would be expected — for example, comparing a column against a subquery that computes an average, like finding all products priced above the average price of all products.

Correlated subqueries. A correlated subquery references a column from the outer query, meaning it can't be evaluated independently — it's effectively re-evaluated for each row the outer query considers, referencing that row's specific values. This makes correlated subqueries more powerful for row-by-row comparisons, but also generally more expensive to execute than a non-correlated subquery, since the subquery logic runs repeatedly rather than once.

Subqueries versus joins. Many tasks that can be accomplished with a subquery can also be expressed using a join, and the two approaches sometimes have different performance characteristics depending on the specific database and query. As a general pattern, joins are often preferred when you need to retrieve columns from both tables in the result, while subqueries are often clearer when you only need to filter or compute a value based on a relationship between tables without needing to display columns from both.

Subqueries in the `FROM` clause. A subquery can also be used as if it were a table in the FROM clause (sometimes called a derived table), letting you first compute an intermediate result set and then query against that result as though it were a regular table — useful for breaking a complex query into more understandable, composable pieces.

Common pitfalls. A common mistake is using a subquery that returns multiple rows in a context that expects a single value (a scalar context), which causes an error. Another is writing an unnecessarily correlated subquery when a simpler, non-correlated version (or a join) would produce the same result with better performance.

The operators that pair with subqueries. Several SQL operators exist largely to work with subquery results. IN checks whether a value appears in the set a subquery returns, and NOT IN checks the opposite — with the caveat that a NULL in the subquery's result can make NOT IN behave in unintuitive ways. EXISTS checks only whether a subquery returns any rows at all, which often pairs naturally with correlated subqueries and can be more efficient because the database can stop at the first match. ANY and ALL compare a value against every value a subquery returns — "greater than all of them," "equal to any of them" — completing a small vocabulary worth knowing as a set.

A worked example in words. Consider "find employees who earn more than their department's average salary." The outer query walks the employees; for each one, a correlated subquery computes the average salary of that employee's department; the WHERE clause keeps rows where the employee's salary exceeds that computed average. Reasoning through it inside-out — what the inner query computes, what single value or set it hands back, and how the outer query uses it — is the reliable way to read any subquery, and writing them follows the same order: inner query first, tested on its own, then wrapped.

Readability and modern alternatives. When subqueries nest more than a level or two deep, queries become genuinely hard to read. Modern SQL offers common table expressions — the WITH clause — which let you name an intermediate result and then use it like a table, expressing the same logic as stacked FROM-clause subqueries but in a flat, top-to-bottom order that reads like a sequence of steps. Knowing subqueries remains essential — CTEs are built on exactly the same concept — but recognizing when a WITH clause would say the same thing more clearly is part of writing SQL other people can maintain.

Understanding subqueries — how to write them, where they can be used, and when they're the clearer choice compared to a join — is an important step beyond basic single-table queries toward writing more expressive, capable SQL for real-world, multi-table data problems.