Recursive CTE in SQL: 5 Real-World Examples (Employee Trees, Date Series, Graph Paths)

The Structure: Anchor + Recursive Part

A recursive CTE has two halves: an anchor member (the starting rows) and a recursive member (which references the CTE itself, adding rows until none are new). MySQL 8+, PostgreSQL, SQL Server, and SQLite all support them. The skeleton: WITH RECURSIVE name AS (SELECT ... anchor UNION ALL SELECT ... FROM name JOIN ...) SELECT * FROM name;

Example 1: The Employee Org Tree

WITH RECURSIVE org AS (SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.id) SELECT name, level FROM org ORDER BY level; — anchor is the CEO (no manager); recursion walks the tree adding one level per pass. Add GROUP BY level to count headcount per level, or a path column to reconstruct reporting lines.

Example 2: Expand a Date Range into Rows

WITH RECURSIVE dates AS (SELECT DATE('2026-01-01') AS d UNION ALL SELECT d + INTERVAL 1 DAY FROM dates WHERE d < DATE('2026-01-31')) SELECT d FROM dates; — the classic date-series generator. Use it to left-join against sparse sales data so every day appears (even days with no sales), the foundation of correct trend charts and date-based reporting.

Example 3: Bill of Materials (Multi-Level Product Structure)

WITH RECURSIVE bom AS (SELECT part_id, parent_id, qty, 1 AS depth FROM parts WHERE parent_id IS NULL UNION ALL SELECT p.part_id, p.parent_id, p.qty * b.qty, b.depth + 1 FROM parts p JOIN bom b ON p.parent_id = b.part_id) SELECT part_id, SUM(qty) FROM bom GROUP BY part_id; — quantity multiplies at each level, so the final SUM gives the total of each component needed for one finished product. This single pattern powers manufacturing planning and cost roll-ups.

Example 4: Graph Paths (Who Can Reach Whom)

WITH RECURSIVE paths AS (SELECT start_node, end_node, 1 AS hops FROM edges WHERE start_node = 'A' UNION ALL SELECT p.start_node, e.end_node, p.hops + 1 FROM paths p JOIN edges e ON p.end_node = e.start_node) SELECT end_node, MIN(hops) FROM paths GROUP BY end_node; — BFS-style traversal for network analysis, referral chains, and dependency graphs. Add WHERE hops < 10 to cap depth, and a visited set to prevent cycles.

Example 5: Fibonacci (The Sanity Check)

WITH RECURSIVE fib AS (SELECT 0 AS n, 1 AS next UNION ALL SELECT next, n + next FROM fib WHERE n < 100) SELECT n FROM fib; — the shortest recursive CTE, perfect for verifying your engine handles recursion. MySQL and SQLite cap recursion depth (default 1000 in MySQL); set cte_max_recursion_depth if you need more.

The Infinite-Loop Trap (and the Fix)

Every recursive CTE must terminate: the recursive member needs a WHERE condition that eventually stops producing rows, and data with cycles (A manages B, B manages A) will loop forever without a visited guard. The fixes: add a depth column and cap it (WHERE depth < 20), or track a path string and exclude nodes already visited. If your query hangs, those are the first two things to check — every SQL developer hits this once.