Recursive CTEs

Recursive CTEs are incredibly useful for querying hierarchical data like org charts or category trees.

Organization Chart Example

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 as level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;