Guide
SQL Subqueries and CTEs: A Practical Guide
Master scalar subqueries, derived tables, NOT IN vs NOT EXISTS, and WITH clauses through real SQL interview patterns.
Where subqueries live
A subquery can appear in SELECT (a scalar expression), FROM (a derived table that must have an alias), and WHERE (with IN, NOT IN, EXISTS, or a comparison to a scalar). Each position has its own rules and pitfalls.
The 'second highest' pattern is a scalar subquery in WHERE: MAX(salary) below the MAX of the table. The 'above average' pattern compares each row to a scalar subquery. The 'customers who did X' pattern uses IN or EXISTS.
NOT IN vs NOT EXISTS vs LEFT JOIN ... IS NULL
NOT IN is broken when the subquery can return NULL: the whole comparison becomes UNKNOWN and the query returns nothing. NOT EXISTS and the LEFT JOIN anti-join handle NULLs correctly. Prefer them when the right side has nullable columns.
CTEs make multi-step problems readable
A CTE names an intermediate result: WITH step1 AS (...), step2 AS (...) SELECT ... FROM step2. Multi-stage problems — dedupe, number, filter — become a chain of named steps instead of nested parentheses. CTEs and derived tables are equivalent in SQLite; CTEs just read better.
Correlated subqueries
A correlated subquery references the outer query and runs once per outer row. They are powerful for row-by-row comparisons but can be slow; window functions or joins often express the same intent more efficiently.