Some questions have no fixed depth. "List everyone who reports to the principal, directly or indirectly" might mean two levels in one college and six in another. "Show every date in November, even days with no sales" needs rows that are not stored anywhere. A recursive CTE handles both: it starts from a few rows and keeps feeding its own output back into itself until no new rows appear. MySQL supports it from version 8.0 with WITH RECURSIVE.
The two parts of every recursive CTE
WITH RECURSIVE nums AS (
SELECT 1 AS n -- 1. anchor member: runs once
UNION ALL
SELECT n + 1 FROM nums -- 2. recursive member: refers to nums
WHERE n < 5 -- stop condition
)
SELECT n FROM nums;
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
How MySQL evaluates it:
| Iteration | Input rows (from previous step) | New rows produced |
|---|---|---|
| Anchor | none | 1 |
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 5 |
| 5 | 5 | none (5 < 5 is false), so recursion stops |
Each iteration sees only the rows produced by the previous iteration, not the whole result so far. The final result is all rows from every step combined.
Example 1: a reporting hierarchy with levels and paths
CREATE TABLE staff (
id INT PRIMARY KEY, name VARCHAR(20), role VARCHAR(25), manager_id INT
);
INSERT INTO staff VALUES
(1,'Ramesh','Principal',NULL),
(2,'Sneha','HOD Computer Science',1),
(3,'Imran','HOD Electronics',1),
(4,'Tanvi','Assistant Professor',2),
(5,'Gopal','Lab Assistant',4),
(6,'Ritu','Assistant Professor',3);
WITH RECURSIVE org AS (
SELECT id, name, role, 1 AS level,
CAST(name AS CHAR(200)) AS path
FROM staff
WHERE manager_id IS NULL -- start at the top
UNION ALL
SELECT s.id, s.name, s.role, o.level + 1,
CONCAT(o.path, ' > ', s.name)
FROM staff s
JOIN org o ON s.manager_id = o.id -- children of rows found so far
)
SELECT name, role, level, path FROM org ORDER BY path;
| name | role | level | path |
|---|---|---|---|
| Ramesh | Principal | 1 | Ramesh |
| Imran | HOD Electronics | 2 | Ramesh > Imran |
| Ritu | Assistant Professor | 3 | Ramesh > Imran > Ritu |
| Sneha | HOD Computer Science | 2 | Ramesh > Sneha |
| Tanvi | Assistant Professor | 3 | Ramesh > Sneha > Tanvi |
| Gopal | Lab Assistant | 4 | Ramesh > Sneha > Tanvi > Gopal |
Sorting by path puts each person under their manager, which is handy for an indented org chart in a report. To list only one branch, change the anchor, for example WHERE name = 'Sneha'.
Why the CAST is there
Without CAST(name AS CHAR(200)), the query fails:
ERROR 1406 (22001): Data too long for column 'path' at row 1
MySQL fixes each column's type from the anchor member. The anchor's name is VARCHAR(20), so path becomes 20 characters wide and the longer concatenated paths do not fit. Always widen text columns that grow during recursion.
Example 2: walking up instead of down
Who is in Gopal's chain of command? Reverse the join: follow manager_id upwards.
WITH RECURSIVE chain AS (
SELECT id, name, manager_id FROM staff WHERE name = 'Gopal'
UNION ALL
SELECT s.id, s.name, s.manager_id
FROM staff s
JOIN chain c ON s.id = c.manager_id
)
SELECT name FROM chain;
| name |
|---|
| Gopal |
| Tanvi |
| Sneha |
| Ramesh |
The same pattern gives breadcrumbs for nested product categories (Electronics > Mobiles > Accessories) or threaded comment replies.
Example 3: generating a date series
WITH RECURSIVE days AS (
SELECT DATE('2026-11-01') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-11-07'
)
SELECT d, DAYNAME(d) AS day_name FROM days;
| d | day_name |
|---|---|
| 2026-11-01 | Sunday |
| 2026-11-02 | Monday |
| 2026-11-03 | Tuesday |
| 2026-11-04 | Wednesday |
| 2026-11-05 | Thursday |
| 2026-11-06 | Friday |
| 2026-11-07 | Saturday |
LEFT JOIN your sales table onto this list and days without sales show up as zero instead of disappearing. A full worked report using this trick is in CROSS JOIN in MySQL.
Limits and safety nets
If the stop condition is missing or wrong, MySQL does not loop forever:
WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums)
SELECT COUNT(*) FROM nums;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth to a larger value.
The default cte_max_recursion_depth is 1000. When you genuinely need more (a year of dates is 365, but a list of 50,000 numbers is not), raise it for your session only: SET SESSION cte_max_recursion_depth = 100000;. The max_execution_time setting (milliseconds, applies to SELECT) is a second safety net.
Common mistakes
- Cycles in the data. If A reports to B and B reports to A, the recursion only stops at the depth limit. Guard with a path check: add
AND FIND_IN_SET(s.id, o.visited) = 0and carry a comma-separatedvisitedlist of IDs. - Using
UNIONinstead ofUNION ALLwithout a reason.UNIONremoves duplicate rows at every step, which costs time; use it only when duplicates would cause endless repetition. - Aggregates in the recursive member.
SUM(),GROUP BY, window functions,DISTINCTandORDER BYare not allowed there. Aggregate in the finalSELECT. - Referring to the CTE twice in the recursive member, or inside a subquery there. MySQL allows only one direct reference, in the
FROMclause. - Missing index on the parent column. Each iteration joins on
manager_id; on large tables, index it.
Recursive CTEs build on ordinary ones, so if WITH itself is new, start with common table expressions in MySQL. For one-level parent lookups, a self join is simpler, and the list of what else MySQL 8 added is in MySQL 8 new features.