Common Table Expressions — naming a subquery

A CTE doesn't do anything a subquery can't already do — it just gives a subquery a name and a spot before the main query, which turns out to matter a lot once a query needs more than one derived result, or needs to reference itself.

Intermediate

4 min read

The readability problem CTEs solve

-- Nested subquery version — reads inside-out, and gets worse with each added layer
SELECT * FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE order_count > 5;

-- The same query, as a CTE — reads top-to-bottom, in the order it actually executes
WITH customer_totals AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE order_count > 5;

A Common Table Expression (WITH name AS (subquery)) is exactly the derived-table subquery from its own lesson, just written differently: named up front with WITH, before the main query references it, instead of nested inline inside the FROM clause. Both versions produce identical results and identical query plans in most databases — the CTE version's real advantage is purely readability, reading top-to-bottom in roughly the order the logic actually happens, rather than requiring the deeply-nested version to be read from the inside out.

Where CTEs actually earn their keep: more than one derived result

WITH large_orders AS (
    SELECT * FROM orders WHERE amount > 1000
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS order_count FROM large_orders GROUP BY customer_id
)
SELECT customers.name, customer_totals.order_count
FROM customers
JOIN customer_totals ON customers.id = customer_totals.customer_id;

Multiple WITH clauses, separated by commas, each name their own derived result — and later CTEs can reference earlier ones (customer_totals here reads from large_orders, not from orders directly). Expressing this as nested nested subqueries would mean a subquery nested inside another subquery nested inside the main query — each additional named CTE keeps this genuinely readable in a way that keeps nesting subqueries structurally cannot, once there's more than one derived intermediate result involved.

Recursive CTEs: querying a hierarchy

WITH RECURSIVE employee_chain AS (
    -- base case: the starting employee
    SELECT id, name, manager_id FROM employees WHERE id = 5
 
    UNION ALL
 
    -- recursive case: each employee's manager, added one level at a time
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_chain ec ON e.id = ec.manager_id
)
SELECT * FROM employee_chain;

A RECURSIVE CTE has the exact same two-part shape as the recursion lesson's base case and recursive case, just expressed in SQL: the first SELECT is the base case (start at employee 5), and the part after UNION ALL is the recursive case, referencing the CTE's own name (employee_chain) to build on rows already found, one level at a time, until no new rows are produced. This is genuinely the only practical way to query an arbitrary-depth hierarchy — an employee's full management chain, a category tree with unknown nesting depth, a bill-of-materials with subcomponents — directly in SQL, since a fixed number of JOINs can only reach a fixed number of levels deep.

Why a recursive CTE actually terminates

Round 1: employee_chain = {employee 5}
Round 2: employee_chain += {employee 5's manager}
Round 3: employee_chain += {that manager's manager}
...
Stops when: the recursive SELECT produces zero new rows (reached the top, no manager_id)

The recursion happens by repeatedly running the "recursive case" query against only the rows added in the previous round, stopping automatically once a round produces no new rows — this mirrors the recursion lesson's base case exactly: without a genuine stopping condition (here, eventually reaching a row with no manager, so the join finds nothing further), a recursive CTE would loop forever the same way an un-based recursive function would.

CTEs vs. a derived table in FROM: when to reach for which

-- Simple, single derived result — a plain subquery is fine either way
SELECT * FROM (SELECT ...) AS t WHERE ...;
 
-- Multiple derived results, or one referencing another, or genuine recursion — CTE clearly wins
WITH a AS (...), b AS (SELECT ... FROM a) SELECT * FROM b;

For one simple derived table, a plain nested subquery and a CTE are close to interchangeable — pick whichever is more readable in context. The CTE genuinely earns its complexity the moment there's more than one derived intermediate result, one derived result needs to reference another, or the query needs actual recursion — none of which nested subqueries alone can express cleanly, and the last of which (recursion) they can't express at all.