Subqueries
A query used as a value, a table, or a predicate. Correlated vs uncorrelated is the performance fork.
Level: intermediate
What is it?
A subquery is a query nested inside another SQL statement. It can return a scalar value, a single-column set for IN/ANY/ALL, a row relation in FROM, or a Boolean condition through EXISTS.
Why does it matter?
Subqueries express dependencies between query results and can make existence tests, comparisons, and derived relations easier to read. Their performance depends on correlation, indexes, cardinality, and optimizer transformations.
Mental model
First identify how the inner query is used. An uncorrelated subquery can often be evaluated independently; a correlated one references outer rows, though optimizers may rewrite it as a join or another plan.
How it works
- Scalar subqueries must meet the database's cardinality rules, often returning at most one row and one column.
- EXISTS tests whether the inner query returns any row, and the projection value itself is usually irrelevant.
- Correlated subqueries reference outer-query columns; logical correlation does not always imply literal repeated execution.
- NOT IN can behave unexpectedly if the subquery returns NULL because SQL comparisons use three-valued logic; NOT EXISTS is often safer for anti-joins.
- The optimizer may decorrelate or transform subqueries, so inspect the plan instead of assuming a fixed execution pattern.
Steps
- Write the inner query independently and verify its intended result shape.
- Connect it to the outer query through a scalar comparison, IN, EXISTS, or FROM relation.
- Check NULL, empty-set, and multiple-row behavior.
- Use EXPLAIN to see how the DBMS executes the final query on representative data.
Real-world usage
- Find customers who have at least one order using EXISTS.
- Compare a row against an aggregate or retrieve a derived table for subsequent joins.
Common mistakes
- Using NOT IN without accounting for NULLs in the inner result.
- Assuming every correlated subquery executes once per outer row.
- Writing a scalar subquery that unexpectedly returns multiple rows.
Interview questions
When is EXISTS useful?
When the query only needs to know whether at least one matching row exists, especially for semijoin/anti-join logic.
Why can NOT IN return no rows unexpectedly?
If the inner set contains NULL, comparisons can become UNKNOWN; WHERE keeps only TRUE. A carefully correlated NOT EXISTS often expresses the intended condition better.