Recursive CTEs

Traverse hierarchical data like org charts and trees.

How it works

Recursive CTEs reference themselves. They have a base case (the starting rows) and a recursive case (how to find the next level). They're the standard way to traverse trees and graphs in SQL — org charts, comment threads, bill-of-materials, category hierarchies.

WITH RECURSIVE org AS (
  -- Base: start with the CEO
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

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

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;