Common Table Expressions (CTEs) and Recursion

A common table expression, or CTE, lets you give a name to an intermediate query result and then use that name in a later query. Mechanically it is a subquery that you can read top to bottom. Practically it is one of the biggest jumps in SQL readability you can make.

CTEs are not a vendor extension. They entered the standard with SQL:1999, which, as Markus Winand's modern SQL reference puts it, “added the with clause to define ‘statement scoped views’”. PostgreSQL has supported them, recursion included, since version 8.4 in 2009.

Basic shape

WITH recent_orders AS (
  SELECT user_id, SUM(total) AS spend_30d
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, r.spend_30d
FROM users u
JOIN recent_orders r ON r.user_id = u.id
ORDER BY r.spend_30d DESC
LIMIT 20;

The part inside WITH ... AS ( ... ) is just a named subquery. The main query below is where you consume it.

CTE vs subquery

Every CTE can be rewritten as an inline subquery. What CTEs buy you is:

Linear reading order. You read the steps top to bottom, not inside-out.
Names for intermediates. Your future self understands the query faster.
Multiple references. You can use the same CTE twice in the main query without repeating the expression.

Query planners used to materialize CTEs eagerly, which made them slower than subqueries. Modern PostgreSQL, SQL Server, and Snowflake generally inline CTEs unless you force materialization; PostgreSQL made inlining the default for single-reference CTEs in version 12 (2019), and its documentation spells out the override: “You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query.” Treat that as an implementation detail; write the clearest query first, profile if it matters.

Multiple CTEs

WITH
  active_users AS (
    SELECT id FROM users WHERE last_login > NOW() - INTERVAL '7 days'
  ),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS n
    FROM orders
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
  )
SELECT u.id, COALESCE(r.n, 0) AS orders_7d
FROM active_users u
LEFT JOIN recent_orders r ON r.user_id = u.id;

You can define any number of CTEs and each one may reference the ones before it.

Recursive CTEs

Recursive CTEs traverse hierarchical data: org charts, category trees, threaded comments, bill of materials. The shape is always the same: an anchor query plus a recursive query unioned together.

-- Every manager above a given employee
WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 0 AS depth
  FROM employees
  WHERE id = 42                       -- anchor: the employee we start from

  UNION ALL

  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON c.manager_id = e.id -- recursive step
)
SELECT * FROM chain ORDER BY depth;

Rules of thumb for recursive CTEs:

Always guard with a depth cutoff or cycle check on data you do not fully trust. Cycles will loop forever.
Use UNION ALL, not UNION. UNION deduplicates on every iteration and is dramatically slower.
The recursive part must reference the CTE itself; the anchor part must not.

Common mistakes

Forgetting a , between multiple CTEs.
Putting a semicolon before the main query. The CTE and the query that consumes it are one statement.
Writing a recursive CTE without a base case. Your query runs forever or hits the recursion limit.

Practice this live

SQL Protocol's campaign and Interview Mode currently drill the pieces a CTE organizes: joins, aggregation, and subqueries. That is the right order to learn them, because every CTE is just a named subquery. Once nested subqueries feel routine, rewriting them as a WITH clause is a readability upgrade, not a new concept.

CTEs: Readable SQL at Scale