CTEs & Subqueries

Common table expressions name intermediate results with WITH. Their main value is readability: a query built as four named steps is reviewable, and the same logic nested four subqueries deep is not. In an interview, a CTE chain also lets you narrate your reasoning step by step, which is most of what is being assessed.

Recursive CTEs walk hierarchies — an org chart, a category tree, a chain of referrals — with an anchor member and a recursive member that references the CTE itself.

Correlated subqueries reference the outer query and are evaluated per outer row, which is why they can be slow. Many are better expressed as a join or a window function. "Each employee earning more than their department average" is a correlated subquery in older SQL and one window function today.

EXISTS, IN, and JOIN for existence checks. EXISTS short-circuits on the first match and handles NULLs correctly. IN is fine over a small, NULL-free list. A JOIN will duplicate outer rows if the inner side matches more than once, so it is the wrong tool for "does a match exist" unless you deduplicate.

`NOT IN` with NULLs is the classic trap. If the subquery returns any NULL, NOT IN yields no rows — because "is this value not equal to NULL" is unknown, never true. NOT EXISTS behaves the way you meant.

Prefer a CTE to a repeated subquery. Writing the same subquery twice invites them to drift apart when someone edits one.

Watch out for CTE materialisation. Some engines optimise across the boundary and some materialise each CTE, so a CTE referenced three times may be computed three times — or once and reused. If a query is unexpectedly slow, that boundary is worth checking.