MySQL Recursive CTE with Examples

A recursive common table expression (CTE) is a WITH query that refers to its own name. In MySQL, recursive CTEs can generate a sequence or traverse hierarchical data such as an employee reporting tree. They are supported in MySQL 8.0 and later.

Every recursive CTE needs an initial result, a recursive query that reads rows from the CTE, and a condition that eventually stops producing rows. For the general WITH syntax and nonrecursive examples, see MySQL CTEs.

MySQL recursive CTE syntax

Use WITH RECURSIVE before the CTE definitions:

WITH RECURSIVE cte_name (column_name, ...) AS (
    -- Anchor member: create the initial row or rows
    SELECT ...
    UNION ALL
    -- Recursive member: produce the next rows
    SELECT ...
    FROM cte_name
    WHERE stop_condition
)
SELECT ...
FROM cte_name;

The first query is the anchor member. It does not read from the CTE and creates the starting rows. The recursive member reads the previous iteration’s rows and produces the next ones. Recursion ends when the recursive member returns no rows.

The column names and types are based on the anchor member. Use a column list to make the names clear, and make sure the anchor expressions have suitable types for all values produced later.

Generate a number sequence

This query returns the integers from 1 through 10:

WITH RECURSIVE numbers (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n
FROM numbers
ORDER BY n;

The anchor starts with 1. Each recursive iteration adds one to the prior value. Once n reaches 10, WHERE n < 10 is false, so the next iteration returns no rows and the recursion stops. The outer ORDER BY controls the displayed order.

Traverse an employee hierarchy

A common hierarchy design stores each employee’s manager in the same table. Here is a small example table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INT NULL
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
    (1, 'Avery Chen', NULL),
    (2, 'Morgan Lee', 1),
    (3, 'Jordan Kim', 1),
    (4, 'Taylor Ray', 2),
    (5, 'Casey Park', 3);

To return employee 1 and everyone who reports to them, directly or indirectly, use a recursive CTE:

WITH RECURSIVE org_chart (employee_id, employee_name, manager_id, depth) AS (
    SELECT employee_id, employee_name, manager_id, 0
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, org_chart.depth + 1
    FROM employees AS e
    JOIN org_chart
        ON e.manager_id = org_chart.employee_id
)
SELECT employee_id, employee_name, manager_id, depth
FROM org_chart
ORDER BY depth, employee_id;

The anchor selects the starting employee. Each recursive pass finds employees whose manager_id matches an employee from the previous pass. depth is 0 for the starting employee and increases for each level below them. This example assumes the employee hierarchy has no reporting cycles.

Stop conditions and recursion limits

Always design the recursive member to stop. In the number example, WHERE n < 10 provides a clear stopping condition. In the hierarchy example, the query stops when no additional employees report to the current level. A cycle in hierarchical data can keep producing rows, so check that the relationships are valid before running a traversal.

MySQL also limits recursive depth as a safety measure. The default value of cte_max_recursion_depth is 1000. Reaching that limit is not a substitute for a correct stop condition; increasing it can allow a runaway query to run longer.

Common recursive CTE errors

• Missing RECURSIVE: If the CTE refers to itself, start the clause with WITH RECURSIVE.
• No termination condition: Make the recursive member eventually return no rows. For hierarchies, avoid cycles or explicitly guard against them.
• Unexpected duplicate rows: UNION ALL retains rows from every iteration. UNION DISTINCT removes duplicate complete rows, but rows that differ in a column such as depth are still distinct.
• Sorting or aggregation in the recursive member: MySQL restricts constructs such as ORDER BY, GROUP BY, and aggregate functions in the recursive query. Apply ordering or aggregation in the outer query when possible.

For the full list of rules, see the MySQL 8.0 Reference Manual: Recursive Common Table Expressions.