Common Table Expressions

Overview

A Common Table Expression (CTE) is a named, temporary result set defined with the WITH keyword that exists only for the duration of a single statement. CTEs make complex queries readable by breaking them into logical steps and can be referenced multiple times. Recursive CTEs let you traverse hierarchies such as org charts or category trees.

Syntax / Usage

Define one or more CTEs with WITH name AS (...), then reference them in the main query. A recursive CTE has an anchor member and a recursive member combined with UNION ALL.

-- Basic CTE
WITH high_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, hs.spent
FROM high_spenders hs
JOIN users u ON u.id = hs.user_id;

-- Recursive CTE
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Examples

Chain multiple CTEs to compute a report step by step:

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
ranked AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, revenue - prev_revenue AS growth
FROM ranked;

Walk a category tree from the root down using recursion:

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

Common Mistakes

• Forgetting RECURSIVE when the CTE references itself
• Writing a recursive CTE with no terminating condition, causing an infinite loop
• Assuming a CTE is always materialized (databases may inline it, affecting performance)
• Referencing a CTE before it is defined in the WITH list
• Expecting a CTE to persist across statements (it only lives for one query)

See Also

sql-subqueries select sql-window-functions