Common Table Expressions

Last module ended with a warning: nested subqueries force you to read from the middle outward. A common table expression, or CTE, fixes the reading order by giving each step a name and putting the steps in sequence.

WITH step_one AS (...),
     step_two AS (...)
SELECT ... FROM step_two;

Same result as nesting. Much easier to read, debug and change.

Naming the steps#

Earlier you found customers who spent more than the average buyer. As a CTE:

WITH customer_spend AS (
    SELECT o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.customer_id
)
SELECT c.full_name, s.total
FROM customer_spend s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total > (SELECT AVG(total) FROM customer_spend)
ORDER BY s.total DESC;

 full_name  | total
------------+--------
 Farhan Ali | 882.00
 Aisha Khan | 726.00
 Grace Lin  | 607.50

customer_spend is defined once and used twice, in the join and in the average. With subqueries you would have copied the whole block. When the definition changes, you change it in one place.

Chaining CTEs#

Each CTE can use the ones above it. This is how you build up a calculation in stages.

WITH monthly AS (
    SELECT DATE_TRUNC('month', o.order_date)::date AS month,
           SUM(oi.quantity * oi.unit_price)        AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY 1
),
with_prev AS (
    SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly
)
SELECT month, revenue, revenue - prev_revenue AS change
FROM with_prev
ORDER BY month;

   month    | revenue | change
------------+---------+---------
 2024-02-01 |  439.00 |
 2024-03-01 |  501.50 |   62.50
 2024-04-01 | 1229.50 |  728.00
 2024-05-01 |  710.00 | -519.50
 2024-06-01 |  259.50 | -450.50

Read it like a recipe. Compute monthly revenue. Attach the previous month. Subtract. Each block does one job, and you can test it by temporarily selecting from it. That last point is the real daily benefit: when a result looks wrong, run SELECT * FROM monthly; and check the stage in isolation.

Are CTEs temporary tables?#

No, and the distinction affects performance.

Before PostgreSQL 12, every CTE was an optimization fence: the database computed it fully, stored the result, and then read from that. It never pushed outer filters into it.

From version 12 on, a CTE that is non-recursive, has no side effects and is referenced only once gets inlined into the outer query, as if you had written a subquery. A CTE referenced more than once is computed once and reused.

You can override it either way:

WITH recent AS MATERIALIZED     (SELECT ... ),   -- always compute once and store
     lookup AS NOT MATERIALIZED (SELECT ... )    -- always inline

Use MATERIALIZED when the CTE is expensive and used several times, or when you want a stable snapshot. Use NOT MATERIALIZED when you want the planner to push a filter inside it. Other databases differ: SQL Server inlines CTEs, older MySQL did not support them at all.

Recursive CTEs#

A recursive CTE refers to itself. It is the standard way to walk hierarchies of unknown depth: org charts, category trees, bill-of-materials, threaded comments.

The structure has two parts joined by UNION ALL.

WITH RECURSIVE org AS (
    -- Anchor: where the walk starts
    SELECT employee_id, full_name, manager_id,
           1 AS depth,
           full_name::text AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: attach the next level of reports
    SELECT e.employee_id, e.full_name, e.manager_id,
           org.depth + 1,
           org.path || ' > ' || e.full_name
    FROM employees e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, path FROM org ORDER BY path;

 depth |                   path
-------+------------------------------------------
     1 | Meera Iyer
     2 | Meera Iyer > Arjun Rao
     3 | Meera Iyer > Arjun Rao > Kenji Watanabe
     3 | Meera Iyer > Arjun Rao > Sofia Martins
     2 | Meera Iyer > Omar Haddad
     3 | Meera Iyer > Omar Haddad > Grace Thompson
     2 | Meera Iyer > Priya Nair
     3 | Meera Iyer > Priya Nair > Daniel Okafor
     3 | Meera Iyer > Priya Nair > Lena Fischer
     2 | Meera Iyer > Rahul Verma
(10 rows)

Sorting by the path string lays the tree out in reading order.

How it executes#

The database does not recurse the way a function does. It iterates, and each round works only on the rows produced by the previous round.

The union of all the rounds is the final result. It stops when a round produces nothing.

Walking upward#

The same pattern climbs. Starting at Grace Thompson and following manager_id toward the root:

WITH RECURSIVE chain AS (
    SELECT employee_id, manager_id, 0 AS hops
    FROM employees WHERE employee_id = 9
    UNION ALL
    SELECT e.employee_id, e.manager_id, chain.hops + 1
    FROM employees e
    JOIN chain ON e.employee_id = chain.manager_id
)
SELECT employee_id, hops FROM chain;

 employee_id | hops
-------------+------
           9 |    0
           8 |    1
           1 |    2

Grace (9) reports to Omar (8), who reports to Meera (1). Two hops to the top.

Infinite loops#

If your data contains a cycle (A manages B, B manages A), the recursion never runs out of rows and keeps going until memory or patience ends. Two defenses:

-- A depth limit in the recursive term
... JOIN org ON e.manager_id = org.employee_id WHERE org.depth < 20

-- PostgreSQL 14+: built-in cycle detection
WITH RECURSIVE org AS (...)
CYCLE employee_id SET is_cycle USING cycle_path
SELECT ...

Put a guard on any recursive CTE that walks data you do not fully control.

Type matching#

The anchor and recursive parts must agree on column types. full_name is VARCHAR(100), while path || ' > ' || ... yields TEXT, so the anchor above casts with ::text. Skip it and PostgreSQL reports recursive query "org" column 5 has type character varying(100) in non-recursive term but type text overall. The fix is always a cast in the anchor.

Generating rows#

Recursion can also produce a sequence:

WITH RECURSIVE n AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;     -- 1, 2, 3, 4, 5

In PostgreSQL, generate_series(1, 5) does this directly and is simpler. The recursive form matters on databases that lack it, and as a model for the pattern.

Data-modifying CTEs#

PostgreSQL lets INSERT, UPDATE and DELETE live inside a WITH, using RETURNING to pass rows along. It moves data in one atomic statement:

WITH moved AS (
    DELETE FROM orders_staging
    WHERE order_date < DATE '2024-01-01'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

The rows are deleted from one table and inserted into the other as a single statement, with no window where they exist in neither (orders_staging and orders_archive are illustrative names).

Common mistakes#

Treating a CTE as a cache. In older PostgreSQL it was one by accident. Today it may be inlined and run once per reference if you mark it NOT MATERIALIZED. Do not rely on either.

Recursion without a stop. Cyclic data plus no guard means a query that never returns.

Mismatched types between anchor and recursive term. Cast in the anchor.

One giant CTE doing everything. The point is small named steps. If a CTE needs three paragraphs of comments, split it.

Using UNION instead of UNION ALL in the recursion. It deduplicates on every round, which is slower and usually unnecessary.

Practice#

1. Rewrite the "products priced above their category average" query using a CTE that computes the category averages first.
2. Build a CTE chain that finds each customer's total spend, then labels each 'high' (over 500), 'medium' (200 to 500) or 'low'. Aisha Khan should be 'high'.
3. Using the recursive org query as a starting point, list everyone who sits under Priya Nair at any depth. You should get 2 rows.
4. Write the upward walk for employee 3 (Sofia Martins) and confirm she is two hops from Meera.
5. Inside a transaction, set Arjun Rao's manager_id to 3 (Sofia Martins, who reports to him) to create a cycle. Run the upward walk from employee 3 with LIMIT 10 on the outer query, watch it loop, then add a depth guard and ROLLBACK.