Common Table Expressions

Chapter 14 of the SQL for Beginners series combined finished queries with set operators, but both sides still had to be written out in full everywhere they were needed. Chapter 15 of this 24-chapter series gives a query a name: the WITH clause, or common table expression (CTE), defines an intermediate result once so you can read it several times — and with WITH RECURSIVE, generate rows that walk a hierarchy or build a sequence.

Naming a query with WITH

A CTE sits before the main statement and behaves exactly like a table for everything that follows. Here the messy part — summing each order’s items — gets the name order_totals, and the outer query joins it as if it had been in the schema all along:

WITH order_totals AS (
    SELECT order_id, CAST(SUM(quantity * unit_price) AS INTEGER) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT o.id, o.status, ot.total
FROM orders o
JOIN order_totals ot ON ot.order_id = o.id
ORDER BY ot.total DESC, o.id;

Expected output:

id | status | total
6 | paid | 1798
4 | shipped | 1347
1 | paid | 997
2 | shipped | 899
3 | cancelled | 749
5 | paid | 649

Functionally this is the derived table from Subqueries — the difference is where the logic lives. It is written once, named, and read like a table, so the outer query stays about the question rather than the arithmetic. A WITH must come before the main SELECT; there is no valid way to slip one into the middle of a statement.

Chaining CTEs for multi-step reports

Separate CTEs with commas, and each one may use every CTE defined above it — the report equivalent of intermediate variables. Spend per order feeds spend per customer in a single readable statement:

WITH order_totals AS (
    SELECT order_id, CAST(SUM(quantity * unit_price) AS INTEGER) AS total
    FROM order_items
    GROUP BY order_id
),
customer_totals AS (
    SELECT o.customer_id, SUM(ot.total) AS spent
    FROM order_totals ot
    JOIN orders o ON o.id = ot.order_id
    GROUP BY o.customer_id
)
SELECT c.first_name, c.last_name, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
ORDER BY ct.spent DESC, c.id;

Expected output:

first_name | last_name | spent
Aarav | Sharma | 1896
Amara | Ochieng | 1798
Lena | Fischer | 1347
Priya | Nair | 749
Wei | Ling | 649

Aarav leads because his two orders (997 and 899) add up to 1896, no two customers tie so c.id never has to break a draw, and Marc is absent once more — a customer with no orders contributes no row to an inner join. Each layer is small enough to test on its own: comment out the last CTE and query order_totals directly.

WITH RECURSIVE: counting rows into existence

Add the RECURSIVE keyword and a CTE may reference itself. The shape is always the same: an anchor that produces the first rows, UNION ALL, then a recursive term that reads the CTE’s own output and emits the next batch until a WHERE stops it. The smallest useful example numbers rows without window functions:

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 6
)
SELECT n FROM seq;

Expected output:

n
1
2
3
4
5
6

Round one emits 1; round two reads 1 and emits 2; and so on until a row arrives with n = 6, which the WHERE rejects and the recursion ends. Join that sequence to a table and it becomes a row counter for data that has no natural numbering:

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 6
)
SELECT s.n, c.first_name, c.last_name
FROM seq s
JOIN customers c ON c.id = s.n;

Expected output: six rows, 1 Aarav Sharma through 6 Amara Ochieng — SQLite, MySQL and PostgreSQL all accept the query as written (MySQL requires version 8.0 or newer, SQLite 3.8.3 or newer).

Walking a hierarchy

Self-joins in the recursive term are where recursion earns its keep: expand one level of a tree per round. The query below starts at customer 1, adds his orders as level 1, then each order’s items as level 2 — carrying order_id so every arm knows what it hangs from:

WITH RECURSIVE path(level, node, order_id) AS (
    SELECT 0, 'customer: ' || first_name || ' ' || last_name, NULL
    FROM customers WHERE id = 1
    UNION ALL
    SELECT 1, 'order ' || o.id || ' (' || o.status || ')', o.id
    FROM orders o WHERE o.customer_id = 1
    UNION ALL
    SELECT p.level + 1, '- ' || pr.name, p.order_id
    FROM path p
    JOIN order_items oi ON oi.order_id = p.order_id
    JOIN products pr ON pr.id = oi.product_id
    WHERE p.level = 1
)
SELECT level, node FROM path ORDER BY level, node;

Expected output:

level | node
0 | customer: Aarav Sharma
1 | order 1 (paid)
1 | order 2 (shipped)
2 | - Clean Code
2 | - The Silent Patient
2 | - The Very Hungry Caterpillar

The WHERE p.level = 1 guard is the stopping condition: only level-1 rows expand further, so recursion terminates after one pass. Change id = 1 to id = 3 and the tree shrinks to four lines — customer: Lena Fischer, order 4 (shipped), - Matilda, - The Silent Patient. Comment trees, category menus and org charts are all this pattern with different labels.

How often does a CTE actually run?

A CTE referenced twice looks like one computation, but SQLite is free to inline it and run it twice if that suits the plan. Ask two questions of the same CTE and the numbers must still agree:

WITH order_totals AS MATERIALIZED (
    SELECT order_id, CAST(SUM(quantity * unit_price) AS INTEGER) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT (SELECT COUNT(*) FROM order_totals) AS buckets,
       (SELECT SUM(total) FROM order_totals) AS revenue;

Expected output:

buckets | revenue
6 | 6439

Six distinct orders, 6439 in revenue — the same totals chapter 13 derived a different way. The MATERIALIZED keyword (SQLite 3.35+, also PostgreSQL 12+) forces the single evaluation you just saw; NOT MATERIALIZED invites inlining. PostgreSQL materialised every CTE before version 12, MySQL 8.0 reaches similar decisions internally, and older MySQL versions do not support the syntax at all. The rule of thumb: leave the keyword off until a plan shows repeated work.

CTE vs subquery vs view

The three express the same idea — a named result — at different scopes:

• Derived table: anonymous, inline, visible only at that exact spot; fine when it is used once and read in place.
• CTE: named for the duration of one statement; the place for multi-step reports and recursion.
• View: stored in the database catalog under a name, reusable by every future query and permission-checked like a table — the subject of Views and Query Reuse.

Nothing about a CTE is persisted: run the statement, and the name exists only while the statement does.

Common mistakes

• Putting WITH after the SELECT — it must open the statement (or nest entirely inside a subquery); anywhere else is a syntax error.
• Referencing a CTE defined later in the chain — each CTE may only see the ones declared before its comma, so reorder the definitions.
• Recursive term with no stopping condition — rows multiply forever: MySQL stops itself at cte_max_recursion_depth (1000 by default), PostgreSQL 14+ at max_recursion_depth (100), while SQLite keeps generating until you interrupt it.
• Sorting inside the CTE and expecting it to show — row order is not inherited through a name in any dialect; put the ORDER BY in the outer query.
• Assuming a CTE always runs once — SQLite may inline and re-evaluate a body referenced several times; add MATERIALIZED when duplicate work matters.
• Mismatched columns between anchor and recursive arm — both sides of the UNION ALL must return the same columns in the same order, or the statement will not parse.

Practice checklist

1. Rewrite the derived-table query from chapter 13 as the named order_totals CTE and confirm both print the same six totals.
2. Run the chained report, then add WHERE ct.spent > 1000 to it and check that only Aarav, Amara and Lena remain.
3. Change the recursion guard from n < 6 to n < 3, predict the output, and run it to confirm three rows.
4. Re-run the hierarchy query for customer 3 and explain why its tree has exactly four lines.
5. Run the materialised query twice — with and without the keyword — and prove the results are identical.

What's next?

CTEs name intermediate results for the length of one statement; the next construct keeps rows side by side with the groups they summarise — ranking, running totals and moving averages in Window Functions.