Recursive CTEs for Hierarchies

Why you can't just "join N times"

Some tables point at themselves. An employees row has a manager that is another emp_id; a categories row has a parent_id that is another id. That models a tree of unknown depth: Electronics > Audio > Headphones > Over-ear today, one level deeper tomorrow. To walk it with plain joins you would need one JOIN per level, but you do not know the level count when you write the query. A recursive CTE solves this by applying one query to its own output over and over until it stops producing rows.

The mental model: anchor, recursive member, working table

Read WITH RECURSIVE as a loop with three moving parts.

WITH RECURSIVE org AS (
  -- ANCHOR: the seed rows, run once
  SELECT emp_id, name, 0 AS depth
  FROM employees
  WHERE manager IS NULL              -- the CEO(s)

  UNION ALL

  -- RECURSIVE MEMBER: run repeatedly, one level deeper each pass
  SELECT e.emp_id, e.name, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager = o.emp_id
)
SELECT * FROM org ORDER BY depth, emp_id;
-- Ada 0 | Bob 1 | Cy 1 | Di 2 | Fay 2 | Eve 3

The engine keeps a working table. The anchor fills it once. Then each pass runs the recursive member against only the rows the previous pass produced, not the whole accumulated result, appends the new rows, and repeats. When a pass yields zero rows (every leaf is reached), the loop stops. That "previous pass only" rule is the whole model: it is why depth + 1 counts levels correctly and why a clean tree terminates on its own.

Carry values down the recursion

Anything you want per node, compute it in the anchor and thread it through the recursive member. A breadcrumb plus its root ancestor:

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, name AS breadcrumb, name AS root_name, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id,
         t.breadcrumb || ' > ' || c.name,   -- append this level's name
         t.root_name,                        -- copy the root down unchanged
         t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT breadcrumb, root_name, depth FROM tree ORDER BY depth, id;
-- Electronics > Audio > Headphones | Electronics | 2

breadcrumb grows one segment per level; root_name, set once in the anchor, rides along untouched.

Pitfalls

• Cycles loop forever. If dirty data has A's parent as B and B's parent as A, no pass ever returns zero rows. SQLite will not rescue you here: it has no default recursion-depth limit, so an unguarded cycle runs until it exhausts memory (in the browser it just freezes the tab). Add your own guard in the recursive member, WHERE t.depth < 20, so it stops.
• Join direction decides up vs down. JOIN ... ON c.parent_id = t.id walks parents to children (down). Flip it to c.id = t.parent_id and you climb toward the root instead. Getting this backward is the classic first bug.
• Use UNION ALL, not UNION. UNION deduplicates on every pass, which is wasted work on a tree and can hide a cycle you wanted the depth cap to catch.
• Anchor and recursive member must line up in column count and order, and each column's type is fixed by the anchor. On strict engines a too-narrow anchor text column truncates the growing breadcrumb.

Interview nuance: a recursive CTE is not "the CTE can read all of its own rows." Each iteration joins against only the immediately preceding iteration's output (the working table), and default evaluation is breadth-first: all of depth 1, then all of depth 2. That semantics is why depth comes out exact, why the result order without an explicit ORDER BY is level order rather than id order, and why an unbounded self-reference on cyclic data never converges.

Portability: Postgres, MySQL, and BigQuery require the RECURSIVE keyword. SQL Server and Oracle omit it (plain WITH cte AS (...)). SQLite accepts it either way. Write WITH RECURSIVE regardless: it is required on the engines that demand it and documents intent on the ones that do not.

Execution mode: you write a multi-statement script against a fresh in-memory SQLite database that already holds the source tree plus an empty target table; hidden queries then check depths, breadcrumbs, and row counts. Lead your load with DELETE FROM <target>; so a re-run stays idempotent.