MySQL Recursive Queries

A recursive query in MySQL uses a recursive Common Table Expression (CTE) to repeatedly reference itself until a termination condition is met. Recursive queries are used to work with hierarchical or tree-structured data — such as employee-manager hierarchies, category trees, or folder structures — where the depth of the hierarchy is not known in advance.

How Recursive CTEs Work

A recursive CTE has two parts connected by UNION ALL:

1. Anchor Member — The base query that runs once and returns the starting row(s).
2. Recursive Member — The query that references the CTE itself, running repeatedly until no new rows are returned.

Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor: starting point
    SELECT columns FROM table WHERE base_condition

    UNION ALL

    -- Recursive: joins CTE to itself
    SELECT columns FROM table
    JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

Example 1: Employee Hierarchy

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES (1, 'Rajesh Kumar', NULL);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 1);
INSERT INTO employees VALUES (3, 'Karan Das', 1);
INSERT INTO employees VALUES (4, 'Pooja Nair', 2);
INSERT INTO employees VALUES (5, 'Amit Roy', 2);
INSERT INTO employees VALUES (6, 'Deepa Rao', 3);

WITH RECURSIVE org_chart AS (
    -- Anchor: top-level manager (no manager above)
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find direct reports of each employee in the CTE
    SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
    FROM employees AS e
    JOIN org_chart AS oc ON e.manager_id = oc.emp_id
)
SELECT emp_id, emp_name, level
FROM org_chart
ORDER BY level, emp_id;

Result:

emp_id | emp_name     | level
-------+--------------+------
1      | Rajesh Kumar | 1
2      | Sneha Joshi  | 2
3      | Karan Das    | 2
4      | Pooja Nair   | 3
5      | Amit Roy     | 3
6      | Deepa Rao    | 3

The recursive CTE traverses the entire hierarchy automatically, regardless of how deep it is.

Example 2: Generating a Number Series

WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n FROM numbers;

Result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

The anchor returns 1. Each recursive step adds 1 until n reaches 10.

Example 3: Date Series Generation

WITH RECURSIVE date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS dt

    UNION ALL

    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2024-01-07'
)
SELECT dt FROM date_series;

Generates: 2024-01-01, 2024-01-02, ..., 2024-01-07

Controlling Recursion Depth

By default, MySQL allows a maximum recursion depth of 1000. This can be adjusted for very deep hierarchies:

SET SESSION cte_max_recursion_depth = 5000;

Key Points

• Recursive CTEs use WITH RECURSIVE and reference themselves via UNION ALL.
• The anchor member provides the starting rows; the recursive member builds on them.
• Recursion stops automatically when the recursive member returns no new rows.
• Common use cases include hierarchies, number/date series, and path traversal in trees.
• The default recursion limit is 1000 — adjust with cte_max_recursion_depth if needed.