Mastering MySQL Recursive CTEs: Traversing Hierarchies & Generating Series

A Recursive CTE (Common Table Expression) is a subquery that references itself using its own name. It is defined using WITH RECURSIVE and must have a terminating condition. Recursive CTEs are useful for generating series, traversing hierarchical data, and graph traversals.

Syntax

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- Non-recursive (base case): initial rows
 SELECT col1, col2 FROM table_name
 UNION ALL
 -- Recursive case: references cte_name
 SELECT col1, col2 FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

• The first SELECT is the base case provides initial rows.
• UNION ALL adds rows from each iteration (DISTINCT removes duplicates).
• The second SELECT is the recursive case runs until the WHERE condition fails.

Example 1: Generate First 5 Odd Numbers

WITH RECURSIVE odd_no (sr_no, n) AS (
 SELECT 1, 1
 UNION ALL
 SELECT sr_no + 1, n + 2
 FROM odd_no
 WHERE sr_no < 5
)
SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
+-------+---+

Base case returns (1, 1). Each iteration increments sr_no by 1 and n by 2. Recursion stops when sr_no reaches 5.

Example 2: Employee Hierarchy

A more practical use traversing a manager-employee hierarchy ?

-- Assume: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
 -- Base: top-level manager (no manager)
 SELECT id, name, 0
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 -- Recursive: find direct reports
 SELECT e.id, e.name, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

This builds an organizational tree starting from the top-level manager (level 0) and recursively finds all reports at each level.

Key Points

• Always include a terminating condition in the recursive SELECT's WHERE clause to avoid infinite loops.
• MySQL has a default recursion limit of 1000 iterations (configurable via cte_max_recursion_depth).
• Use UNION ALL for performance; UNION DISTINCT only when duplicates must be removed.

Conclusion

Recursive CTEs use WITH RECURSIVE to define self-referencing queries with a base case and recursive case. They are essential for hierarchical data traversal (org charts, category trees), series generation, and graph traversals in MySQL.