Study notes · 12% of the exam

Subqueries, CTEs and Recursive Queries

Know what shape each subquery returns, how IN, EXISTS, ANY and ALL behave with NULLs and empty sets, and how CTEs, data-modifying CTEs and recursive queries are evaluated.

Key points

1
A scalar subquery returns NULL for zero rows and errors for more than one; wrapping a LIMIT/OFFSET query in one turns "no row" into NULL.
2
IN and EXISTS are semi-joins (each outer row at most once); a JOIN repeats the outer row per match.
3
NOT EXISTS and LEFT JOIN … IS NULL are NULL-safe anti-joins. NOT IN / <> ALL return nothing once the subquery yields a NULL.
4
x > ALL (empty) is true and x NOT IN (empty) is true even for NULL x; a NULL in a non-empty set makes ALL and NOT IN unknown.
5
Since PostgreSQL 12, a side-effect-free CTE referenced once is inlined; referencing it twice, a volatile function or MATERIALIZED keeps it as a CTE Scan.
6
Recursive CTEs evaluate an anchor, then repeat the recursive term on the previous iteration's rows; UNION removes duplicates of earlier rows, UNION ALL keeps them.
7
Data-modifying CTEs always run to completion and share one snapshot, so the main query sees changes only through RETURNING.

Common traps

An unqualified column inside a subquery silently resolves to the outer query when the inner table lacks it.
Adding a depth or path column defeats UNION's duplicate elimination; use the CYCLE clause or check the path.
A correlated scalar subquery only errors when a row with several matches appears, so it can pass tests and fail in production.

Read the source

Test yourself on Subqueries, CTEs and Recursive Queries

Ten questions, with the answer and explanation after each one.