Recursive CTEs

Querying Hierarchies

Recursive CTEs let you traverse tree structures like org charts, file systems, or bill-of-materials.

WITH RECURSIVE org AS (
  -- Anchor: start at the root
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports
  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY depth, name;

Purchase this course to unlock the full lesson.

← Previous Next →