DocsRecipesPlaygroundQuery Doctor
Core SQLSubqueries

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_id above). 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 any NULL, NOT IN yields no rows. Use NOT 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.

Type coercion

Why a comparison, sort, or join gives a different answer than expected — implicit type conversion, and why every engine handles it a little differently.

CTE (WITH)

Name a subquery so a query reads top to bottom instead of inside out — plus recursive CTEs for hierarchies and series.

On this page

Four places a subquery can goIn WHERE — a set to test againstIn FROM — a derived tableIn SELECT — a per-row valueCorrelated vs uncorrelatedGotchas