MySQL Recursive CTE (Common Table Expressions)

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.