Subqueries & CTEs

Break complex logic into readable pieces. A subquery is a query inside another query (in FROM, WHERE, or SELECT). A CTE (WITH ...) names a q...

A query nested inside another query. Three common positions: in the FROM clause (derived table), in the SELECT list (scalar subquery, must r...

Predicate that's true if the subquery returns at least one row. The subquery's SELECT list doesn't matter: SELECT 1, SELECT *, SELECT NULL a...

Named query step declared up-front with WITH. Turns a nested subquery into a reusable, top-down read: each CTE is a labeled building block, ...

Subquery that references a column from the outer query, so it's re-evaluated (logically) per outer row. Powerful for per-row comparisons aga...

CTE that references itself: iteration inside a single SQL statement. Structure: an anchor SELECT (the seed rows), UNION ALL, and a recursive...