SQL common table expressions: WITH, readability, and recursion

A common table expression is a named, temporary result set that exists only for the duration of one query. You define it with a WITH clause at the top, give it a name, and then select from it as if it were a table. The point is not new power, it is readability: a CTE lets you name an intermediate step instead of burying it as a nested subquery.

The readability problem CTEs solve

Nested subqueries read inside out. The innermost query runs first conceptually, but it sits furthest from where you start reading. Three levels deep and the logic is hard to follow:

SELECT * FROM (
  SELECT dept, AVG(salary) AS avg_salary FROM (
    SELECT dept, salary FROM employees WHERE active = true
  ) t GROUP BY dept
) d WHERE avg_salary > 50000;

The same logic as CTEs reads top to bottom, each step named:

WITH active_staff AS (
  SELECT dept, salary FROM employees WHERE active = true
),
dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM active_staff GROUP BY dept
)
SELECT * FROM dept_avg WHERE avg_salary > 50000;

Nothing about performance changed. What changed is that a reader can follow the pipeline in order, and each stage has a name that says what it holds.

Chaining and reuse

You can define several CTEs in one WITH, separated by commas, and each may reference the ones defined before it. This turns a tangled query into a readable sequence of transforms. A CTE can also be referenced more than once in the final query, which with a subquery would mean writing the same block twice.

Recursive CTEs

The feature subqueries cannot replace is recursion, which traverses hierarchies: an org chart, a category tree, a bill of materials, a graph of dependencies. A recursive CTE has two parts joined by UNION ALL: an anchor that seeds the result, and a recursive member that references the CTE itself.

WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL      -- anchor: the top
  UNION ALL
  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id          -- recursive: one level down
)
SELECT * FROM chain ORDER BY depth;

The recursive member runs repeatedly, each pass working from the rows the previous pass produced, until it yields no new rows. Always make sure the recursion terminates. A cycle in the data, such as employee A reporting to B reporting to A, loops forever unless you track visited rows or cap the depth.

The materialization caveat

A common assumption is that a CTE always computes once and the result is reused. That is not guaranteed and it varies by engine:

This matters in two directions. Older Postgres materializing a CTE can be slower, because the planner cannot push a filter down into it. Newer Postgres inlining a CTE can re-run an expensive step that you expected to compute once. If a CTE wraps a volatile function or a costly aggregate you reference several times, MATERIALIZED makes the behavior explicit. Do not treat a CTE as a performance tool by default; treat it as a clarity tool and measure if performance matters.

When to use one

Reach for a CTE when a query has distinct logical stages, when you reference the same intermediate result twice, or when you need recursion. Keep plain subqueries for a single trivial filter where a WITH block would be ceremony. Readability is the gain, so use it where readability is actually at stake.

To see a dense query broken into readable, indented stages, paste it into the SQL Formatter, which runs entirely in your browser.