Subqueries
Queries nested inside queries — in SELECT, FROM, and WHERE — and when a correlated one quietly runs per row.
Four places a subquery can go
In WHERE — a set to test against
SQL playground
Loading editor…
⌘/Ctrl + Enter
The subquery here returns one value (a scalar subquery). With IN / NOT IN
/ EXISTS it returns a set.
In FROM — a derived table
SQL playground
Loading editor…
⌘/Ctrl + Enter
Most engines require the derived table to be aliased (high_earners).
In SELECT — a per-row value
SQL playground
Loading editor…
⌘/Ctrl + Enter
Correlated vs uncorrelated
- Uncorrelated — the subquery doesn't reference the outer query. It runs once.
- Correlated — it references an outer column (
e.department_idabove). Logically it runs once per outer row. Optimisers often rewrite these into joins, but not always — a correlated subquery over a big table is a classic slow query.
EXISTS is almost always correlated, and usually the clearest way to ask "is
there a matching row?":
SQL playground
Loading editor…
⌘/Ctrl + Enter
Gotchas
- Scalar subqueries must return 0 or 1 rows. Two rows is a runtime error;
zero rows yields
NULL. NOT IN+NULL. If the subquery returns anyNULL,NOT INyields no rows. UseNOT EXISTS. See rows missing in another table.- A CTE is the same thing, named. Reach for one when a subquery is reused or the nesting gets hard to read.
Hit this as an actual error?
- CTE (WITH) — subqueries lifted out and named.
- WHERE — where IN / EXISTS / scalar comparisons live.
- Rows missing in another table — the anti-join patterns.
- APPLY — T-SQL's way to correlate a subquery with the left row directly, no JOIN condition needed.
Was this page helpful?