Common Table Expression (CTE)

What is a common table expression?

A common table expression, almost always shortened to CTE, is a named temporary result set that lives only for the single SQL statement that defines it. You write it at the top of a query with the WITH keyword, give it a name, then refer to that name in the rest of the query as if it were a real table.

The result exists only while that one statement runs. Nothing is written to the database, no object is created, and the name disappears the moment the query finishes. Think of it as a labelled worksheet you keep beside a calculation: it holds an intermediate step long enough to be useful, then it is gone.

CTEs are part of standard SQL and work in PostgreSQL, MySQL, SQL Server and other major engines, with small syntax differences. Their job is to make a long query readable, whether it runs against a data warehouse or a small application database.

Why CTEs beat nested subqueries

Without a CTE, chaining logic means nesting one subquery inside another. Two levels stay readable. Four levels force you to read inside out, from the innermost bracket back to the outer SELECT, and whoever maintains the query six months later has to do the same.

A CTE flips that around. Each step gets a name at the top of the query, and you read from top to bottom in the order the work actually happens. A query that assembles a fact table for a star schema might filter orders in one CTE, join customers in the next, and aggregate totals in a third. The steps read like the stages of a data pipeline, which is why tools like dbt build their models as a chain of CTEs.

A common real use is filtering on a window function. You cannot put ROW_NUMBER() in a WHERE clause directly, so you compute it inside a CTE first, then filter the named result down to the rows you want, such as the most recent order per customer.

Recursive CTEs and hierarchies

A CTE can also refer to its own output. That variant, written WITH RECURSIVE, lets SQL walk a hierarchy of unknown depth, which a plain query cannot do. It has two parts joined by UNION ALL: an anchor member that produces the starting rows, and a recursive member that references the CTE to produce the next layer. Recursion stops when the recursive member returns no new rows.

The classic case is an org chart. Each employee row points at a manager, and you want everyone below a given boss along with their depth in the tree.

WITH RECURSIVE reports AS (
  SELECT employee_id, manager_id, full_name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.full_name, r.depth + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.employee_id
)
SELECT full_name, depth
FROM reports
ORDER BY depth;

The anchor picks the top of the tree, the person with no manager. Each round then finds everyone who reports to the rows already collected and adds one to the depth counter. The same pattern expands a bill of materials, where a product is built from parts that are themselves built from smaller parts.

A CTE is not a materialized view

Because both hold a result set, CTEs get confused with materialized views. The real difference is persistence. A CTE stores nothing: it is recomputed every time the statement runs and is gone when the statement ends. A materialized view stores its result on disk as a real object, keeps it between queries, and reuses it until you refresh it.

So the choice is about lifespan, not syntax. Reach for a CTE to keep one query readable. Reach for a materialized view when several queries or several people need the same precomputed result and you accept refreshing it on a schedule. When you only need the result to survive for a session, a temporary table sits between the two.

What to watch out for with CTEs

The biggest surprise is performance, and it varies by engine. For years, PostgreSQL treated every CTE as an optimisation fence: it always computed the CTE separately before the rest of the query, which sometimes blocked a faster plan. PostgreSQL 12, released in 2019, changed that. A CTE is now folded into the outer query automatically when it has no side effects, is not recursive, and is referenced only once. You can override the decision with two keywords: MATERIALIZED forces the old separate evaluation, and NOT MATERIALIZED forces inlining.

Other engines behave differently. SQL Server does not store a CTE's result at all: each reference to the same CTE re-runs its definition, so pointing at one CTE three times does the work three times. If that is expensive, a temporary table is often the better tool. When a query runs slow, read its execution plan instead of assuming the CTE is free.

Two smaller points. Naming is the whole benefit, so a stack of CTEs called cte1, cte2 and cte3 throws away the readability you came for. And a CTE is not storage: if you want the result to outlive the statement, write it to a table, a view, or a materialized view.