How Do Recursive CTEs Walk Hierarchies?

A recursive CTE is a query that refers to itself. It has two parts. The anchor is an ordinary SELECT that produces the starting rows. The recursive part is a SELECT that joins the CTE's own name back to the anchor's result, producing the next level, and the engine repeats it until no new rows appear.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  manager_id INTEGER
);

INSERT INTO staff (id, name, manager_id) VALUES
  (1, 'Ada Lovelace', NULL),
  (2, 'Grace Hopper', 1),
  (3, 'Alan Turing',  1),
  (4, 'Katherine Johnson', 2);

WITH RECURSIVE chain (id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM staff
  WHERE manager_id IS NULL

  UNION ALL

  SELECT s.id, s.name, s.manager_id, c.depth + 1
  FROM staff AS s
  JOIN chain AS c ON s.manager_id = c.id
)
SELECT depth, name
FROM chain
ORDER BY depth, name;

Four rows come back: Ada Lovelace at depth 0, Alan Turing and Grace Hopper at depth 1, and Katherine Johnson at depth 2. The anchor found the one person with no manager, and each pass of the recursive part found the next layer down.

The mechanics are worth being precise about. The column list in parentheses after the CTE name — (id, name, manager_id, depth) — declares the shape of the result, and it has to match the anchor's column count and types. The anchor runs once. The recursive part runs repeatedly, with the result of the previous iteration on the right-hand side of the join, and the engine stops when an iteration produces no new rows.

UNION ALL is the usual choice rather than UNION, because UNION would deduplicate each level against the accumulated result — which stops a cycle from looping forever but also removes legitimate duplicate rows and makes the query far slower. If you use UNION to guard against cycles, you need a cycle-detection column in the result, because the deduplication only works if the rows are genuinely distinguishable.

The depth column is not required by the engine, but it is the most useful thing you can add. It gives you the level, it lets you stop the recursion at a maximum depth, and it makes the output order obvious.

Build a path string as you descend. The anchor starts the path with the root category, and each recursive step appends the child name to the path it came from.

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  parent_id INTEGER
);

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics', NULL),
  (2, 'Computers', 1),
  (3, 'Accessories', 1),
  (4, 'Keyboards', 2),
  (5, 'Monitors', 2),
  (6, 'Mechanical Keyboards', 4);

WITH RECURSIVE tree (id, name, parent_id, depth, path) AS (
  SELECT id, name, parent_id, 0, name
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
  FROM categories AS c
  JOIN tree AS t ON c.parent_id = t.id
)
SELECT depth, path
FROM tree
ORDER BY path;

Six rows come back, one per category, each with its depth and its full path from the root: Electronics at depth 0, Electronics > Accessories and Electronics > Computers at depth 1, Electronics > Computers > Keyboards and Electronics > Computers > Monitors at depth 2, and Electronics > Computers > Keyboards > Mechanical Keyboards at depth 3.

The concatenation operator builds the path: t.path || ' > ' || c.name takes the parent's path and appends the child's name. Because the path is carried down the recursion, every row ends up with the complete ancestry rather than just its immediate parent.

Ordering by the path string produces a readable tree, because the separator sorts before any letter, so a parent always appears before its children and siblings appear alphabetically. It is a shortcut, though — a path containing a parent whose name is a prefix of another parent's name can order unexpectedly. If the display order matters, carry a sort key down the recursion as well.

The path column is also what you use for cycle detection. If the data contains a loop — a category whose parent eventually points back to itself — the recursive part never stops finding new rows, and the query either runs until it exhausts memory or until SQLite's recursion limit kicks in. Adding a check that the new id is not already in the current path turns that runaway into a clean error.

There are three mechanisms, and they solve different problems.

Depth limit. Adding a guard on the depth column stops the recursion at a fixed level: WHERE c.depth < 10 inside the recursive part. This bounds the work no matter what the data looks like, which makes it the simplest and most reliable protection for a query that runs in production. It is the right answer when you genuinely only want a few levels — a department tree shown three levels deep, say.

Cycle detection. A depth limit does not help if the data contains a loop, because a loop resets the depth each time around. To detect cycles you carry the path of ids down the recursion and stop when the next id is already on it: WHERE instr(t.path, ',' || c.id || ',') = 0, with the anchor seeding the path as ',' || id || ','. The alternative is to use UNION instead of UNION ALL, which deduplicates each iteration against everything accumulated so far and therefore terminates — but only if the rows are distinguishable, which means you still need a cycle-detection column to be sure.

Anchor restriction. Starting the recursion from a specific node rather than from every root limits the work to one subtree. Combined with a depth limit, that gives you a bounded query over a known region of the tree.

For an interview, the thing to say is that a recursive CTE needs a termination guarantee, and the guarantee has to be in the query rather than assumed from the data. A depth cap is the robust default because it holds even if the data is wrong. Cycle detection is the more precise tool when the hierarchy can legitimately be deep but must not contain loops.

One practical caveat: the depth cap only works if you carry the depth. Since the CTE's column list is fixed, you have to add the depth column to the anchor as 0 and increment it in the recursive part — which is exactly what makes it available as a guard.