Recursive CTEs (WITH RECURSIVE)

Traverse hierarchical and graph data with recursive common table expressions.

Overview

WITH RECURSIVE allows a CTE to reference itself, enabling queries on hierarchical data (trees, graphs) where the depth is unknown at query time. Classic uses: org charts (employee → manager), folder trees, category hierarchies, and graph traversals. A recursive CTE has two parts connected by UNION ALL: the anchor (starting rows) and the recursive term (the join back to the CTE itself). Execution continues until no new rows are produced.

Key Points

•WITH RECURSIVE name AS (anchor UNION ALL recursive_term)
•Anchor: non-recursive starting rows
•Recursive term: JOINs CTE to itself to find next level
•Stops when no new rows are returned
•Use CYCLE detection for graphs with potential cycles
•Depth column: track recursion level for debugging

Examples

Employee hierarchy traversal

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

  UNION ALL

  -- Recursive: employees reporting to previous level
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON ot.id = e.manager_id
)
SELECT REPEAT('  ', depth) || name AS org_chart, depth
FROM org_tree ORDER BY depth, name;

Recursive CTEs (WITH RECURSIVE)

Overview

Key Points

Examples

Exercises

Knowledge Check

What are the two parts of a recursive CTE connected by?