Recursive CTEs

Overview

A recursive CTE uses WITH RECURSIVE to repeatedly reference itself, building a result set iteration by iteration until no new rows are produced. It has two parts joined by UNION ALL: 1. **Anchor member** — the base case, a non-recursive SELECT that produces the starting rows. 2. **Recursive member** — a SELECT that references the CTE itself, joining new rows to the previous iteration's output. The employees table has a manager_id column forming a tree: Sara (CEO, no manager) → Tom, Wendy → Tom manages Uma, Victor; Wendy manages Xavier, Yara. WITH RECURSIVE chain AS ( -- Anchor: start with Sara (the CEO, manager_id IS NULL) SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive: find employees whose manager is in the previous level SELECT e.id, e.name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT name, depth FROM chain ORDER BY depth, name; Iteration 1 (anchor): Sara at depth 1. Iteration 2: Tom, Wendy (manager_id = 1 = Sara) at depth 2. Iteration 3: Uma, Victor (manager = Tom), Xavier, Yara (manager = Wendy) at depth 3. Iteration 4: no more employees found → recursion stops. Result: Sara(1), Tom(2), Wendy(2), Uma(3), Victor(3), Xavier(3), Yara(3). Mental model: the anchor plants a seed row-set, and each recursive step grows it by one layer, like breadth-first search. When a step produces zero new rows, the loop ends.

Hierarchical data (or a hierarchy like org charts, category trees, folder structures, threaded comments) cannot be traversed to arbitrary depth with a fixed number of JOINs. Recursive CTEs walk the tree to any depth in a single query, without knowing the depth in advance.

Where used: org chart / reporting hierarchy queries, nested category or folder trees, bill-of-materials and dependency graphs

Why learn this

• Traverse tree-structured data (org charts, category hierarchies) to any depth in a single SQL statement
• Replace application-level loops that fire one query per level with a single recursive query — eliminating the N+1 problem for hierarchies

Query walkthrough

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

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

Focus: Watch the anchor plant Sara at depth 1, then each recursive iteration grow the tree by one level — Tom & Wendy at 2, then Uma, Victor, Xavier & Yara at 3 — until no new rows appear.

Common mistakes

• Infinite recursion from missing termination condition: If the data has cycles (e.g. A manages B, B manages A), the recursion never stops. Add a depth counter and a WHERE depth < N guard in the recursive member, or use CYCLE detection (Postgres 14+) to prevent runaway queries.
• Using UNION instead of UNION ALL: UNION deduplicates rows each iteration, which is correct for graph traversal with cycles but slower. For trees (no cycles), always use UNION ALL — it is faster and the standard pattern. Use UNION only if you explicitly need cycle elimination.
• Forgetting WITH RECURSIVE keyword: A plain WITH (non-recursive CTE) cannot reference itself. You must write WITH RECURSIVE to enable the self-reference. Without it, Postgres raises an error about an undefined CTE name.

Glossary

iteration

One complete repetition of a process, like a loop running once to find the next level of data.

traversed

To move through or explore a structure, like following a path through a tree of categories or an org chart.

hierarchy

A system where items are ranked or organized in levels one above the other, like a company org chart.

Recall questions

• What are the two parts of a recursive CTE?
• When does a recursive CTE stop iterating?
• Why is UNION ALL preferred over UNION in recursive CTEs for tree data?

Understanding checks

Which employees appear and at what depth?

Tom (1), Uma (2), Victor (2)

The anchor starts at Tom (depth 1). The recursive step finds employees whose manager_id = Tom's id (2): Uma and Victor at depth 2. No one reports to Uma or Victor, so recursion stops after the next empty iteration.

What is the path array for Xavier?

{Sara,Wendy,Xavier}

The anchor starts with Sara (path = {Sara}). Iteration 2 adds Wendy with path {Sara,Wendy}. Iteration 3 adds Xavier with path {Sara,Wendy,Xavier}. The || operator appends Xavier's name to his manager's accumulated path.

Practice tasks

Change the starting point of the traversal

This recursive CTE starts from the CEO (manager_id IS NULL) and walks the full org tree. Modify the anchor to start from Wendy instead, so the result shows only Wendy and her reports. Keep the depth counter starting at 1 and the ORDER BY.

Challenge

Build the management path for every employee

Write a recursive CTE that, for each employee, builds an array showing the management chain from the CEO down to that employee. Output columns: name, path (an array of names). Start from the root (manager_id IS NULL). Order by name.

Continue learning

Previous: CTEs (WITH)

Previous: Self Join

Return to SQL Roadmap