Recursive CTEs in SQL Explained: Org Charts, BOMs, and Date Spines

What Is a Recursive CTE?

A recursive CTE (Common Table Expression) is a query that references itself, repeating until it runs out of new rows to produce. It's the standard SQL tool for anything with an unknown or variable depth: organizational hierarchies, nested categories, bill-of-materials explosions, graph traversal, or generating a sequence of dates/numbers. Where a self join only reaches one level of a relationship, a recursive CTE walks as many levels as the data actually has — this is exactly why interviewers escalate from "self join" questions to "recursive CTE" questions to test deeper SQL fluency.

Anatomy of a Recursive CTE

Every recursive CTE has exactly three parts, always combined with UNION ALL:

WITH RECURSIVE cte_name AS (
  -- 1. Anchor member: the starting point, runs once
  SELECT ...
  FROM some_table
  WHERE 

  UNION ALL

  -- 2. Recursive member: joins back to the CTE itself, runs repeatedly
  SELECT ...
  FROM some_table t
  JOIN cte_name c ON 
)
-- 3. Final SELECT: reads the fully-built result
SELECT * FROM cte_name;

MySQL and PostgreSQL require the RECURSIVE keyword. SQL Server uses the same WITH cte_name AS (...) syntax but omits the RECURSIVE keyword entirely — the recursive reference alone is enough to trigger recursive evaluation.

How it actually executes: the anchor runs once to produce the first batch of rows. Then the recursive member runs repeatedly, each time using only the rows produced in the previous iteration (not the whole accumulated result) as its input, until an iteration produces zero new rows — at which point recursion stops.

Example 1: Organizational Hierarchy

-- employees: manager_id points back to another row in the same table
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- anchor: top of the org

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id   -- recursive: one level down each pass
)
SELECT * FROM org_chart ORDER BY depth, name;

For a full walkthrough of this specific pattern, including counting reports and finding all-levels-deep subordinates, see our dedicated guide on self joins and hierarchical data.

Example 2: Bill of Materials (Nested Parts Explosion)

A very common manufacturing/inventory pattern: a product is made of sub-components, which are themselves made of sub-components, to arbitrary depth.

-- parts: each part optionally has a parent_part_id and a quantity needed
| part_id | part_name    | parent_part_id | quantity |
|---------|--------------|-----------------|----------|
| 1       | Bicycle      | NULL            | 1        |
| 2       | Frame        | 1               | 1        |
| 3       | Wheel        | 1               | 2        |
| 4       | Spoke        | 3               | 32       |
| 5       | Tire         | 3               | 1        |

WITH RECURSIVE parts_explosion AS (
  SELECT part_id, part_name, parent_part_id, quantity, 1 AS level
  FROM parts
  WHERE part_id = 1   -- start from the Bicycle

  UNION ALL

  SELECT p.part_id, p.part_name, p.parent_part_id,
    p.quantity * pe.quantity AS quantity,   -- multiply quantities down the tree
    pe.level + 1
  FROM parts p
  JOIN parts_explosion pe ON p.parent_part_id = pe.part_id
)
SELECT * FROM parts_explosion ORDER BY level;

-- Result shows total quantities needed at every depth, e.g. 64 spokes
-- (32 per wheel × 2 wheels) computed automatically by the recursive multiplication

This "multiply a running value down the tree" technique — carrying quantity forward and compounding it each recursive step — is a strong pattern to have ready; it generalizes to any weighted hierarchy, not just manufacturing.

Example 3: Generating a Date Spine

Recursive CTEs aren't limited to hierarchical data in a table — they're also the standard portable way to generate a sequence of numbers or dates, useful for filling in missing dates in a report (see our gaps and islands guide for why this matters).

WITH RECURSIVE date_spine AS (
  SELECT DATE '2026-01-01' AS d

  UNION ALL

  SELECT d + INTERVAL '1 day'
  FROM date_spine
  WHERE d < DATE '2026-01-31'
)
SELECT * FROM date_spine;

-- Produces one row per day for the entire month, with no dependency on any table

PostgreSQL's generate_series() is simpler for this specific case, but the recursive CTE version works on any database that supports WITH RECURSIVE, including MySQL 8+ and SQL Server.

Preventing Infinite Recursion

If the underlying data has a cycle (a data-quality bug where an employee is accidentally set as their own indirect manager, for example), a naive recursive CTE will loop forever, or until it hits a safety limit. Guard against this explicitly:

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS visited
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.visited || e.id
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
  WHERE NOT e.id = ANY(oc.visited)   -- stop if we've already visited this id
)
SELECT * FROM org_chart;

Most databases also offer a blunt safety net: PostgreSQL doesn't cap recursion by default (it will error out on excessive memory use), while SQL Server defaults to a 100-iteration limit via MAXRECURSION (adjustable, or disabled with OPTION (MAXRECURSION 0)). Relying on that limit as your only safeguard is a common mistake — always add an explicit cycle guard or a hard depth cap (WHERE depth < 20) when the data's integrity isn't guaranteed.

Recursive CTE vs. Self Join vs. Application-Level Loop

• Self join — sufficient only when you need exactly one level of relationship (an employee's direct manager). Cannot express "all levels" in a single non-recursive query.
• Recursive CTE — the right tool whenever depth is unknown or variable, and you want the traversal to happen inside the database in one round trip.
• Application-level loop — sometimes chosen when the traversal needs complex per-level business logic, external API calls, or when the hierarchy is enormous and better paginated level by level. Usually slower due to repeated round trips, but sometimes more maintainable for complex logic.

Performance Considerations

• Index the column used in the recursive join condition (manager_id, parent_part_id) — without it, every recursive step does a full table scan.
• Recursive CTEs process one level per iteration; very deep hierarchies (thousands of levels) can be slow simply due to iteration count, not query complexity.
• Always test against realistic depth and breadth — a recursive query that's instant on 6 levels of test data can behave very differently on a production hierarchy with genuine cycles or much greater depth.

Common Interview Questions

1. What are the three parts of a recursive CTE? Anchor member, recursive member, and the UNION ALL connecting them — see the anatomy section above.
2. How would you detect and prevent infinite recursion in a hierarchy with a data-quality bug? Track visited IDs (as shown above) or enforce a maximum depth guard.
3. When would you choose a recursive CTE over a self join? Whenever the number of levels to traverse isn't fixed or known ahead of time.
4. Can you use UNION instead of UNION ALL in a recursive CTE? Some databases (like PostgreSQL) do support UNION for deduplication between iterations, but it's slower since every step must check the entire accumulated result for duplicates — UNION ALL is standard unless you specifically need dedup and have a cycle guard as a safety net regardless.

Frequently Asked Questions

Do recursive CTEs work in MySQL?

Yes, since MySQL 8.0. Earlier versions have no recursive CTE support at all — hierarchical queries had to be done with stored procedures or application-level loops.

Is a recursive CTE the same as a loop in a programming language?

Conceptually similar — each iteration builds on the previous one — but it's declarative, not imperative: you describe the anchor and the recursive step, and the database engine handles the iteration and termination internally.

Practice Recursive CTEs

Recursive CTEs are one of the highest-leverage advanced SQL skills to master, since they unlock an entire category of hierarchical and sequence-generation problems. Try them hands-on with our SQL practice questions, or apply them to a full case study in our case studies. For the ranking functions used alongside recursive queries, see our complete guide to window functions.