CTEs: naming the steps

A query you can read a year later

A CTE (a WITH clause) gives a name to an intermediate result:

WITH recent_orders AS (
  SELECT * FROM orders
  WHERE placed_at >= current_date - interval '90 days'
    AND status = 'paid'
),
per_customer AS (
  SELECT customer_id, count(*) AS orders, sum(amount) AS spend
  FROM recent_orders
  GROUP BY customer_id
)
SELECT c.full_name, p.orders, p.spend
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
WHERE p.spend > 50000
ORDER BY p.spend DESC;

Read it top to bottom: take recent paid orders, summarise them per customer, attach names, keep the big spenders. Four sentences, in the order a person would say them. The same logic as nested subqueries is the same length and takes five times as long to understand, because you have to read it inside out.

Later CTEs can reference earlier ones, which is what makes them compose. This is the single biggest readability improvement available in SQL, and it costs nothing.

What a CTE is not

It is not a variable, and it is not stored. It exists for the duration of the one statement. Running the query again recomputes everything.

It is also not necessarily evaluated once. Older Postgres treated every CTE as an optimisation fence: always materialised, always exactly once, never merged into the outer query. Since version 12 the planner may inline a CTE that is referenced once, which is usually faster and occasionally changes behaviour you were relying on. You can force either:

WITH heavy AS MATERIALIZED ( ... )      -- compute once, keep the result
WITH light AS NOT MATERIALIZED ( ... )  -- inline it into the outer query

Reach for MATERIALIZED when a CTE is expensive and used several times. Reach for NOT MATERIALIZED when a CTE selects from a large table and the outer query has a filter that could have used an index: materialising it first would read the whole table before filtering.

Other engines differ. MySQL 8 and SQLite support CTEs; older MySQL does not have them at all, and derived tables in the FROM clause are the portable fallback.

Recursive CTEs

WITH RECURSIVE builds a result from itself, which is how you walk a tree or generate a series:

WITH RECURSIVE months AS (
  SELECT date '2026-01-01' AS m
  UNION ALL
  SELECT (m + interval '1 month')::date FROM months WHERE m < date '2026-12-01'
)
SELECT * FROM months;

An anchor row, a rule for the next row, and a stopping condition. Leave out the stopping condition and it runs forever, or until the server runs out of memory, which is the same thing from your point of view.

In Postgres, generate_series does this particular job better and the next lesson uses it. Recursion earns its place on genuine hierarchies: an org chart, a category tree, a bill of materials, the reply chain of a comment thread.

CTEs that write

In Postgres a CTE can contain INSERT, UPDATE or DELETE with a RETURNING clause, which makes multi-step data changes atomic:

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

The delete and the insert happen in one statement, so there is no window where the rows exist in neither table. This is a useful pattern for archiving and for audit logging, and it is Postgres-specific.

One rule that trips people: all parts of the statement see the same snapshot of the data. A CTE that updates a row and a later part of the same statement that reads that row will not see the update. Two statements in a transaction, not one statement with two CTEs, when you need the second step to see the first.

When a CTE is the wrong tool

Twelve CTEs chained into one enormous statement is not more readable than four, it is a program written in the wrong language. When a query reaches that size, the pieces usually deserve to be views: named, reusable, documented, testable on their own. A view that ten queries share is one definition to fix when the business rule changes; the same logic copied into ten CTEs is ten places to forget.

The rule of thumb: a CTE is for structuring one query. A view is for a definition that more than one query needs.