Subqueries & CTEs
Nest one query inside another, or pull it out as a named CTE (WITH clause) for readability. The tool that turns 200-line queries into 20-line ones.
Sometimes you need the result of one query inside another. SQL offers two ways: **subqueries** (nested parentheses) and **** (Common Table Expressions, named with `WITH`).
Subquery examples: WHERE id IN (SELECT customer_id FROM orders), SELECT (SELECT MAX(price) FROM products) AS max_price, FROM (SELECT ...) AS sub. Subqueries are fine for one-shot cases but nest badly beyond 2 levels — hard to read, hard to debug.
CTE (Common Table Expression) hoists the subquery into a named step: WITH recent_orders AS (SELECT * FROM orders WHERE created_at > now() - interval '30 days') SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id. Reads top-to-bottom like a recipe.
CTEs shine when you need the SAME intermediate result in multiple places, or when breaking a complex query into named steps aids reading. Modern inline CTEs (merge them into the outer query) — so there's usually no performance penalty.
Recursive CTEs solve tree / graph problems: 'find all descendants of this category' — the CTE references itself until a stopping condition. Complex but powerful. Postgres, SQL Server, MySQL 8+ all support it.
Grounded on https://www.postgresql.org/docs/current/queries-with.html
Next up
Indexes & query performance
An index is a secondary data structure that turns O(N) table scans into O(log N) lookups. Know when to add one — and when NOT to.