Subqueries and CTEsLesson 5.4

Recursive CTEs — querying hierarchical data

recursive CTE syntax, anchor member, recursive member, UNION ALL, depth limit, org chart traversal, bill of materials

Recursive CTEs Walk Tree Structures

A recursive CTE consists of two parts joined by UNION ALL: the anchor (starting rows) and the recursive member (rows derived from the previous iteration). The database keeps running the recursive part until it produces no new rows.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level employees with no manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees under each previous level
  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 id, name, depth
FROM org_chart
ORDER BY depth, name;

Preventing Infinite Loops

If your data has circular references (employee A manages B, B manages A), a recursive CTE loops forever. Add a depth limit as a safeguard:

WHERE oc.depth < 10  -- stop at level 10

Recursive CTEs also power bill-of-materials queries (components containing components) and path-finding in graph data stored in relational tables.