Common Table Expressions (CTEs)

Common Table Expressions (CTEs) let you name a subquery and reference it by name within a larger query. The primary benefit is readability: a complex query with three nested subqueries becomes three named, independently-understandable steps. CTEs don’t add power you couldn’t get with subqueries, but they make complex queries dramatically easier to write, read, and debug — which matters enormously when a query needs to be maintained over time.

Basic WITH Clause

The simplest CTE replaces a subquery in FROM with a named, readable building block. The difference in clarity compounds as queries grow more complex.

-- Without CTE: the logic is buried and hard to follow
SELECT customer_id, total_spent
FROM (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS totals
WHERE total_spent > 1000;

-- With CTE: reads like prose — name the concept first, then use it
WITH customer_totals AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000;

The CTE is defined in the WITH clause and can be referenced in the main query just like a table.

Chaining Multiple CTEs

Multiple CTEs can be defined in a single WITH clause, and each one can reference those defined before it. This turns complex queries into a sequence of named steps that mirror how you’d describe the logic in plain language — making the query self-documenting.

WITH
-- Step 1: revenue per customer in the past year
customer_revenue AS (
  SELECT customer_id, SUM(total) AS revenue
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '1 year'
  GROUP BY customer_id
),
-- Step 2: classify customers into tiers based on revenue
customer_tiers AS (
  SELECT
    customer_id,
    revenue,
    CASE
      WHEN revenue >= 10000 THEN 'platinum'
      WHEN revenue >= 2500  THEN 'gold'
      WHEN revenue >= 500   THEN 'silver'
      ELSE 'bronze'
    END AS tier
  FROM customer_revenue
),
-- Step 3: summarize how many customers are in each tier
tier_summary AS (
  SELECT tier, COUNT(*) AS customer_count, SUM(revenue) AS total_revenue
  FROM customer_tiers
  GROUP BY tier
)
SELECT * FROM tier_summary ORDER BY total_revenue DESC;

Breaking the logic into named steps makes each piece independently readable and debuggable — you can test each CTE in isolation by temporarily turning it into a standalone query.

MATERIALIZED vs NOT MATERIALIZED

In PostgreSQL 12+, CTEs are inlined by default (treated like subqueries), allowing the planner to push filters down into them. This is usually what you want. Use MATERIALIZED to force the CTE to execute once and cache its results — useful when the CTE is expensive and referenced multiple times, or when inlining produces a worse plan.

-- Force execution once — the result is computed once and reused for both joins
WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, complex_calculation(data) AS score
  FROM raw_events
)
SELECT c.name, e.score
FROM customers c
JOIN expensive_calc e ON e.customer_id = c.id
WHERE e.score > 50;

Use NOT MATERIALIZED to explicitly tell PostgreSQL to inline it (the default since PG12):

WITH simple_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM simple_filter WHERE total > 100;

Recursive CTEs

Recursive CTEs let a query reference itself, enabling traversal of hierarchical or graph-like data that can’t be queried with a flat JOIN. The canonical use cases are org charts, category trees, and path-finding in graphs. Without recursive CTEs, you’d need to either write application code that issues multiple queries or know the depth of the hierarchy in advance.

The structure is always:

WITH RECURSIVE cte_name AS (
  -- Anchor: the starting point (non-recursive, executes once)
  SELECT ...
  UNION ALL
  -- Recursive term: joins the CTE to itself, executes repeatedly until no new rows
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Org Chart Traversal

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       TEXT,
  manager_id INT REFERENCES employees(id)
);

-- Find all reports under employee 1, at any depth
WITH RECURSIVE reports AS (
  -- Anchor: start with the specified root employee
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- Recursive step: add the direct reports of everyone found so far
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT id, name, depth
FROM reports
ORDER BY depth, name;

Generating a Date Series

Recursive CTEs can generate sequences of any kind — dates, integers, intervals. PostgreSQL’s built-in generate_series() is more efficient for this specific case, but the recursive pattern shows the general approach.

-- Generate all dates in January 2024
WITH RECURSIVE dates AS (
  SELECT '2024-01-01'::DATE AS d    -- anchor: start date
  UNION ALL
  SELECT d + 1 FROM dates WHERE d < '2024-01-31'  -- add one day until end date
)
SELECT d FROM dates;

Cycle Detection

Recursive CTEs can loop infinitely if the data has cycles (A → B → A). The safe approach is to track the path of visited nodes and stop when a repeat is detected. PostgreSQL 14+ added a native CYCLE clause, but the manual array approach works on all versions.

WITH RECURSIVE graph AS (
  SELECT from_node, to_node, ARRAY[from_node] AS path
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node, e.to_node, path || e.from_node
  FROM edges e
  JOIN graph g ON g.to_node = e.from_node
  WHERE NOT e.from_node = ANY(path)   -- stop if we've visited this node already
)
SELECT * FROM graph;

CTEs vs Subqueries: When to Use Each

Use a CTE when:

• The same subquery is referenced more than once
• The query has multiple logical steps that benefit from named intermediate results
• You want to make a complex query self-documenting

Use a subquery when:

• The logic is simple and only used once
• You need the planner to push filters into the subquery (avoid MATERIALIZED)
• You’re in an older PostgreSQL version where CTEs were always materialized