Skip to main content

recursive_ctes

🔄 Use Recursive Common Table Expressions (CTEs)​

Recursive CTEs let you traverse hierarchical data (e.g., organizational charts, folder trees) without client-side loops. Define a base case and a recursive union, and SQLite will iterate until no new rows appear. This technique offloads recursion to the database for cleaner SQL.

-- Define a table of employees with manager relationships
WITH RECURSIVE team(id, manager_id, level) AS (
  SELECT id, manager_id, 0 FROM employees WHERE id = 1  -- CEO
  UNION ALL
  SELECT e.id, e.manager_id, t.level + 1
  FROM employees e
  JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team ORDER BY level;