Recursive CTEs

Some data is naturally hierarchical or self-referencing — an employee’s manager is also an employee, a category can contain subcategories, a part in a bill of materials can be made of other parts. Regular SQL struggles with data like this because you don’t know in advance how many levels deep it goes. A recursive CTE solves exactly this problem by letting a query reference itself, one level at a time, until there is nothing left to expand.

The WITH RECURSIVE syntax

A recursive CTE has two parts joined by UNION ALL: an anchor member that produces the starting rows, and a recursive member that repeatedly builds on the previous result until it produces no more new rows:

Recursive CTE skeleton

SQL

WITH RECURSIVE cte_name AS (
  -- Anchor member: the starting point
  SELECT ...
  FROM some_table
  WHERE ...

  UNION ALL

  -- Recursive member: references cte_name itself
  SELECT ...
  FROM some_table
  JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Worked example: an organization chart

Suppose an employees table stores each employee’s manager as a manager_id referencing another row in the same table:

id	name	manager_id
1	Sana (CEO)	NULL
2	Marco	1
3	Priya	1
4	Devon	2
5	Elin	4

To list every employee under Sana, along with how many levels below her they sit:

Traversing the reporting hierarchy

SQL

WITH RECURSIVE org_chart AS (
  -- Anchor: start at the top of the hierarchy
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find employees whose manager is already in org_chart
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT name, depth
FROM org_chart
ORDER BY depth, name;

    name     | depth
-------------+-------
 Sana (CEO)  |     0
 Marco       |     1
 Priya       |     1
 Devon       |     2
 Elin        |     3

How it executes, step by step

The anchor member runs once and produces the initial rows (Sana, at depth 0)
The recursive member runs repeatedly, each time joining employees against only the rows produced in the previous iteration
Round 1 finds Marco and Priya (their manager, Sana, was in the previous result)
Round 2 finds Devon (his manager, Marco, was found in round 1)
Round 3 finds Elin (her manager, Devon, was found in round 2)
Round 4 finds no new rows, so the recursion stops and all accumulated rows are returned

Other classic use cases

Category trees — a product category that can have nested subcategories to any depth
Bill of materials — a manufactured part composed of other parts, which may themselves be composed of parts
Graph traversal — finding all nodes reachable from a starting node, such as all servers dependent on a given service
Generating a sequence — recursive CTEs are also a handy way to generate a numeric or date series without a helper table

Always define a proper base case and termination condition

The anchor member must produce a finite starting set, and each recursive iteration must eventually produce zero new rows, otherwise the recursion runs forever (or until it hits a database-imposed recursion limit and errors out). This is especially easy to get wrong with cyclic data — for example, if two employees mistakenly report to each other. Some databases let you guard against this with an explicit recursion depth limit:

PostgreSQL: guarding against unexpected cycles

SQL

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
  WHERE oc.depth < 50 -- safety valve against runaway recursion
)
SELECT * FROM org_chart;

UNION ALL, not UNION

Recursive CTEs almost always use UNION ALL rather than plain UNION. UNION would try to de-duplicate rows on every iteration, which is both slower and can interfere with the recursion producing the rows you expect.

WITH RECURSIVE lets a query build on its own previous results, iteration by iteration
An anchor member provides the starting rows; a recursive member repeatedly extends them until no new rows are produced
Always ensure the recursion has a real termination condition — cyclic or unbounded data can cause it to run indefinitely