How to Write Recursive SQL for Hierarchical Data

Most business data is hierarchical. Employees report to managers. Product categories nest inside parent categories. Parts contain sub-parts. And if you've ever tried to flatten one of these trees with a regular JOIN, you've hit the wall: you don't know how deep the tree goes, so you don't know how many joins to write.

WITH RECURSIVE exists to solve exactly this. One CTE that references itself, and the database walks the tree for you.

The syntax trips people up the first time, then clicks permanently. Here's how it works.

The pattern: WITH RECURSIVE

Every recursive CTE has the same skeleton:

WITH RECURSIVE tree AS (
  -- Base case: the starting rows (roots, or a specific node)
  SELECT id, name, parent_id, 1 AS depth
  FROM some_table
  WHERE parent_id IS NULL
 
  UNION ALL
 
  -- Recursive step: join back to the CTE itself
  SELECT child.id, child.name, child.parent_id, tree.depth + 1
  FROM some_table child
  JOIN tree ON child.parent_id = tree.id
)
SELECT * FROM tree;

The base case runs once. Then the database takes whatever rows it produced, feeds them into the recursive step, takes those results, feeds them back in, and keeps going until the recursive step returns zero rows. That's the termination condition, and it's implicit — you don't write a BREAK or a WHERE to stop it (though you should add depth limits, which I'll cover below).

Two things to remember: the recursive step can only reference the CTE once (no self-self-joins), and the column count and types between the base case and the recursive step must match exactly.

1. Org chart: all reports for a manager

Given a standard employees table:

-- employees(id, name, manager_id, department)
 
WITH RECURSIVE reports AS (
  -- Start with direct reports of manager #4
  SELECT id, name, manager_id, department, 1 AS depth
  FROM employees
  WHERE manager_id = 4
 
  UNION ALL
 
  SELECT e.id, e.name, e.manager_id, e.department, r.depth + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT id, name, department, depth
FROM reports
ORDER BY depth, name;

Swap WHERE manager_id = 4 for WHERE manager_id IS NULL and you get the full tree from the CEO down. The depth column tells you how many levels deep each person is, which is useful for indenting the output or filtering to "show me only the first three levels."

2. Category tree: build the full breadcrumb path

E-commerce and content platforms almost always have a categories table with a parent_id column. The typical question is "given a leaf category, what's its full path?" — like Electronics > Computers > Laptops.

-- categories(id, name, parent_id)
 
WITH RECURSIVE breadcrumb AS (
  -- Start at the target leaf
  SELECT id, name, parent_id, name::text AS full_path
  FROM categories
  WHERE id = 47  -- "Laptops"
 
  UNION ALL
 
  SELECT c.id, c.name, c.parent_id,
         c.name || ' > ' || b.full_path
  FROM categories c
  JOIN breadcrumb b ON b.parent_id = c.id
)
SELECT full_path
FROM breadcrumb
WHERE parent_id IS NULL;

This one walks up the tree instead of down — the recursive step joins on b.parent_id = c.id, climbing from child to parent. The WHERE parent_id IS NULL in the final SELECT grabs only the root-level row, which by that point has accumulated the complete path string.

If you need paths for all categories at once, flip it: start from the roots (WHERE parent_id IS NULL) and concatenate downward. That's more efficient than running the upward walk once per leaf.

3. Bill of materials: total cost of a composite part

Manufacturing and SaaS pricing both have the same structure: a thing is made of other things, which are made of other things. A parts table with a self-referencing parent_part_id and a quantity multiplier:

-- parts(id, name, parent_part_id, unit_cost, quantity)
 
WITH RECURSIVE bom AS (
  SELECT id, name, unit_cost, quantity, unit_cost * quantity AS line_cost,
         1 AS depth
  FROM parts
  WHERE parent_part_id = 100  -- top-level assembly
 
  UNION ALL
 
  SELECT p.id, p.name, p.unit_cost, p.quantity,
         p.unit_cost * p.quantity AS line_cost,
         b.depth + 1
  FROM parts p
  JOIN bom b ON p.parent_part_id = b.id
)
SELECT name, unit_cost, quantity, line_cost, depth
FROM bom
ORDER BY depth, name;
 
-- Total cost of the assembly:
-- SELECT sum(line_cost) FROM bom;

The line_cost at each level is unit_cost * quantity. Sum it at the end to get the total material cost for the top-level assembly. If sub-parts share components, they'll appear multiple times in the output with their respective quantities — that's correct, because you need N of them at each point in the tree.

4. Date series generator (no generate_series needed)

Postgres has generate_series(), but MySQL doesn't, and neither does every data warehouse. A recursive CTE fills the gap:

WITH RECURSIVE date_range AS (
  SELECT DATE '2026-01-01' AS dt
 
  UNION ALL
 
  SELECT dt + INTERVAL '1 day'
  FROM date_range
  WHERE dt < DATE '2026-12-31'
)
SELECT dt FROM date_range;

In MySQL, set cte_max_recursion_depth or you'll hit the default 1000-row limit for anything longer than ~2.7 years. Postgres doesn't have a hard cap, but it will happily generate a million rows if you let it, so add the WHERE bound.

I use this constantly for gap-filling in time series — left join your revenue or event data against the date spine and COALESCE missing days to zero. It's one of those patterns that's simpler than it looks once you've written it twice.

Depth limiting and cycle detection

Two failure modes will ruin your day with recursive CTEs.

Runaway depth. If someone accidentally sets an employee's manager_id to a valid ID that creates a 50-level chain (or the data is just legitimately deep), the query runs until the database kills it or your patience runs out. Fix: add a depth counter and filter on it.

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth,
         ARRAY[id] AS path  -- tracks visited nodes
  FROM employees
  WHERE manager_id IS NULL
 
  UNION ALL
 
  SELECT e.id, e.name, e.parent_id, t.depth + 1,
         t.path || e.id
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
  WHERE t.depth < 20              -- hard depth cap
    AND e.id <> ALL(t.path)       -- manual cycle detection
)
SELECT * FROM tree;

The ARRAY[id] column accumulates every node visited along the current path. The e.id <> ALL(t.path) check stops the recursion if it encounters a node it's already visited — that's a cycle. Without this, a circular reference (Alice reports to Bob, Bob reports to Alice) produces an infinite loop that never terminates.

Postgres 14+ has built-in cycle detection with the CYCLE clause:

WITH RECURSIVE tree AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
 
  UNION ALL
 
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
CYCLE id SET is_cycle USING path
SELECT * FROM tree WHERE NOT is_cycle;

Cleaner, and the database handles the array tracking for you. But if you're on Postgres 13 or MySQL, the manual ARRAY approach from the previous example is your only option.

Gotchas worth knowing

UNION ALL vs UNION. Always use UNION ALL in recursive CTEs. Plain UNION deduplicates rows on every iteration, which kills performance — the database has to sort and compare the entire accumulated result set each pass. Worse, in some edge cases the dedup can prevent the recursion from terminating correctly because it silently drops rows the recursive step expected to process.

MySQL's recursion limit. MySQL defaults to cte_max_recursion_depth = 1000. If your hierarchy is deeper (or you're generating a date spine longer than 1000 days), bump it: SET cte_max_recursion_depth = 10000;. Postgres doesn't have an equivalent limit.

MySQL syntax difference. MySQL 8.0+ supports WITH RECURSIVE with the same syntax, but it doesn't support the CYCLE clause or ARRAY types. For cycle detection on MySQL, use a concatenated string path (CONCAT(path, ',', id)) and check with FIND_IN_SET. It's uglier but it works.

Debugging. When a recursive CTE isn't returning what you expect, add depth and path columns even if you don't need them in the final output. Being able to see which path the recursion took to reach a given row saves a lot of staring at the screen.

If you're iterating on these queries, Fastero's SQL editor makes the feedback loop faster — you can version each attempt, cache results on larger datasets, and share the working query with your team without copy-pasting into Slack.

Try Fastero free — connect your database and test recursive CTEs interactively in a schema-aware SQL editor. No credit card required.