Recursive CTEs in SQL: WITH RECURSIVE for Trees, Graphs and Number Series

A plain CTE is just a named subquery. A recursive CTE is a different animal: it can reference itself and run in a loop until the data runs out. That is how SQL walks hierarchies — a tree of reports, a chain of categories, a graph of friends or dependencies. If you have ever written app code that hits the database in a loop asking "and who is that manager's manager?", a recursive CTE collapses the whole thing into a single query.

We'll work through the mechanics on an employees(id, name, manager_id) schema plus orders, and finish with the scariest part: infinite loops.

How WITH RECURSIVE works

A recursive CTE always has two parts joined by UNION ALL:

• The anchor — a starting query that runs exactly once. This is the "root" of the recursion.
• The recursive step — a query that references the CTE's own name. It repeats over and over, each iteration seeing the rows added by the previous one.

WITH RECURSIVE chain AS (
  -- anchor: runs once
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- top-level execs

  UNION ALL

  -- recursive step: references chain
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth, id;

The engine runs the anchor and stores its output in a "working table". Then it runs the recursive step, feeding it the current working table under the name chain. The new rows become the next working table, and the step runs again. The moment an iteration returns zero rows, recursion stops and everything accumulated is handed to the outer query.

The keyword RECURSIVE is written once right after WITH, even if several CTEs are recursive. Without it, Postgres won't let a CTE reference itself.

Walking an org chart (down and up)

The most common case is "show everyone under this manager". The depth column gives you the nesting level, and a built-up path gives the full chain down to each person.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id,
         1 AS depth,
         name::text AS path
  FROM employees
  WHERE id = 1                       -- start from one boss

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         s.depth + 1,
         s.path || ' > ' || e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, depth, path
FROM subordinates
ORDER BY path;

To walk up (from an employee to their managers), just flip the join condition: in the recursive step, instead of "whose manager is the current row", match "who is the current row's manager".

WITH RECURSIVE managers AS (
  SELECT id, name, manager_id
  FROM employees WHERE id = 42

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN managers m ON m.manager_id = e.id   -- climb to the boss
)
SELECT * FROM managers;

Product categories, threaded comments, a bill of materials — they're all the same "a row points at its parent" model, and they all yield to this one template.

Number series and calendars

Recursion doesn't need a source table. You can generate a run of numbers or dates on the fly — handy when you need to "backfill" days that had no orders.

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + 1 FROM days
  WHERE d < DATE '2026-01-31'        -- the stop condition!
)
SELECT d.d, COUNT(o.id) AS orders
FROM days d
LEFT JOIN orders o ON o.created_at::date = d.d
GROUP BY d.d
ORDER BY d.d;

In PostgreSQL a series is usually simpler with generate_series('2026-01-01', '2026-01-31', INTERVAL '1 day') — but recursion is universal and works where generate_series doesn't exist (in plainer engines, say). This example matters for another reason: you invent the stopping rule yourself. Without WHERE d < ... the query would spin forever.

Graphs and cycle safety

A tree is safe: every node has one parent, so there are no cycles. A graph isn't. If a loop sneaks into manager_id (A → B → A), or you traverse a friend/dependency graph, recursion will spin and eat memory.

Postgres 14+ ships built-in protection — CYCLE:

WITH RECURSIVE reachable AS (
  SELECT from_id, to_id
  FROM edges WHERE from_id = 1

  UNION ALL

  SELECT e.from_id, e.to_id
  FROM edges e
  JOIN reachable r ON e.from_id = r.to_id
)
CYCLE to_id SET is_cycle USING path_arr
SELECT DISTINCT to_id FROM reachable WHERE NOT is_cycle;

CYCLE to_id tells Postgres to track already-visited to_id values: on a repeat it marks the row is_cycle = true and stops descending. On older versions you do the same trick by hand — carry an array of visited nodes and filter out repeats:

-- instead of CYCLE: a manual path array
SELECT e.from_id, e.to_id, r.path || e.to_id
FROM edges e
JOIN reachable r ON e.from_id = r.to_id
WHERE e.to_id <> ALL(r.path)        -- don't revisit a node

A classic gotcha: UNION vs UNION ALL. UNION deduplicates rows and so trims some repeats on its own, but it won't save you from cycles whose path/depth differ, and it's slower. Rely on explicit protection (CYCLE or an array), not on UNION's side effect.

Practical guardrails:

• Always keep an explicit stop condition in the recursive step (WHERE depth < 100).
• For graphs, use CYCLE or a visited-nodes array.
• In MySQL 8 the syntax is the same (WITH RECURSIVE), but depth is capped by cte_max_recursion_depth (default 1000) — you hit an error rather than a hang.
• In ClickHouse recursive CTEs arrived late and are limited in places; for hierarchies people often reach for dedicated engines and functions like dictGetHierarchy. Check your version.

A recursive CTE is a for loop hiding inside declarative SQL. Master the anchor + UNION ALL pair, keep the stop condition in mind, and walking trees and graphs stops being a reason to drag logic back into your application.