SQL Tutorial › Subqueries & CTEs › Recursive CTE

SQL Recursive CTE

Q: What is a recursive CTE?

A CTE that references itself. It combines a starting query with a query that repeatedly builds new rows from the previous ones until no more rows are produced.

Q: How do I query a hierarchy in MySQL?

Use WITH RECURSIVE: start from the top (or a chosen node) and join the table to the CTE on the parent column, adding a level or path column as you go.

Q: What is the maximum recursion depth in MySQL?

cte_max_recursion_depth defaults to 1,000. You can raise it for a session with SET SESSION cte_max_recursion_depth = 5000;.

AdvancedLesson 44 of 6912 min read6 queries run on MySQL 8.4

A recursive CTE is a CTE that refers to itself. It starts from a few rows and keeps adding new rows built from the previous ones until nothing new appears. That is exactly what you need to walk a hierarchy of any depth (who reports to whom), to generate a series of numbers or dates, or to follow a chain of links.

In this lesson

Write WITH RECURSIVE with an anchor and a recursive part
Generate number and date series
Walk an employee hierarchy and show levels and paths
Stop runaway recursion

How a recursive CTE works

MySQL

WITH RECURSIVE cte_name AS (  SELECT ...                    -- 1. anchor: the starting rows  UNION ALL  SELECT ... FROM cte_name ...  -- 2. recursive part: new rows from the previous ones)SELECT * FROM cte_name;

MySQL runs the anchor once.
It runs the recursive part on the rows produced in the previous round.
It repeats step 2 until a round produces no rows.
All rounds together are the CTE’s result.

Count from 1 to 10

SQL

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

ResultMySQL 8.4

n
1
2
3
4
5
6
7
8
9
10

10 rows in set

How this query works, step by step

The anchor produces one row: n = 1.
Round 1 reads that row and produces n = 2; round 2 produces 3, and so on.
When n reaches 10, WHERE n < 10 produces no new row, so the recursion stops.
Without that WHERE the recursion would never end (MySQL stops it at 1,000 rounds with an error).

A date series

Every day from 1 to 10 March 2025, joined with the orders, so days without orders show 0:

MySQL

WITH RECURSIVE days AS (  SELECT DATE '2025-03-01' AS d  UNION ALL  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2025-03-10')SELECT days.d, COUNT(o.order_id) AS ordersFROM daysLEFT JOIN orders o ON o.order_date = days.dGROUP BY days.dORDER BY days.d;

ResultMySQL 8.4

d	orders
2025-03-01	0
2025-03-02	0
2025-03-03	0
2025-03-04	0
2025-03-05	0
2025-03-06	0
2025-03-07	0
2025-03-08	1
2025-03-09	0
2025-03-10	0

10 rows in set

How this query works, step by step

The CTE generates 10 consecutive dates.
The LEFT JOIN keeps every date, even those without orders.
Only 8 March has an order (order 1005).

Walk the employee hierarchy

Everyone in the reporting line under the CEO, with their level:

SQL

WITH RECURSIVE org AS (  SELECT emp_id, first_name, manager_id, 1 AS level  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, e.manager_id, org.level + 1  FROM employees e  JOIN org ON e.manager_id = org.emp_id)SELECT emp_id, first_name, manager_id, levelFROM orgORDER BY level, emp_id;

ResultMySQL 8.4

emp_id	first_name	manager_id	level
100	Meera	NULL	1
101	Asha	100	2
105	Vikram	100	2
108	Kavya	100	2
110	Sneha	100	2
102	Rahul	101	3
103	Priya	101	3
106	Neha	105	3
107	Arjun	105	3
109	Rohan	108	3
111	Amit	101	3
104	Karan	102	4

12 rows in set

How this query works, step by step

The anchor finds the top of the tree: the employee with no manager (Meera, level 1).
Round 1 joins employees to the rows found so far and finds people whose manager is Meera: Asha, Vikram, Kavya, Sneha (level 2).
Round 2 finds their reports: Rahul, Priya, Amit, Neha, Arjun, Rohan (level 3).
Round 3 finds Karan, who reports to Rahul (level 4). Round 4 finds nobody, and the recursion ends.

Show the full path

Carry a growing string through the recursion to build an org-chart path:

MySQL

WITH RECURSIVE org AS (  SELECT emp_id, first_name, CAST(first_name AS CHAR(200)) AS path, 0 AS depth  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, CONCAT(org.path, ' > ', e.first_name), org.depth + 1  FROM employees e  JOIN org ON e.manager_id = org.emp_id)SELECT CONCAT(REPEAT('    ', depth), first_name) AS org_chart, pathFROM orgORDER BY path;

ResultMySQL 8.4

org_chart	path
Meera	Meera
Asha	Meera > Asha
Amit	Meera > Asha > Amit
Priya	Meera > Asha > Priya
Rahul	Meera > Asha > Rahul
Karan	Meera > Asha > Rahul > Karan
Kavya	Meera > Kavya
Rohan	Meera > Kavya > Rohan
Sneha	Meera > Sneha
Vikram	Meera > Vikram
Arjun	Meera > Vikram > Arjun
Neha	Meera > Vikram > Neha

12 rows in set

How this query works, step by step

The anchor’s path is CAST(... AS CHAR(200)): the column type is fixed by the anchor, so it must be wide enough for the longest path.
Each round appends > name to the parent’s path.
Sorting by the path puts every person directly under their manager; REPEAT indents by depth.

Watch out

If the anchor column were just first_name (VARCHAR(30)), longer paths would fail with “Data too long”. Always widen string columns in the anchor of a recursive CTE.

Everyone under one manager

All employees below Asha (101), at any depth:

SQL

WITH RECURSIVE team AS (  SELECT emp_id, first_name FROM employees WHERE manager_id = 101  UNION ALL  SELECT e.emp_id, e.first_name FROM employees e JOIN team t ON e.manager_id = t.emp_id)SELECT * FROM team ORDER BY emp_id;

ResultMySQL 8.4

emp_id	first_name
102	Rahul
103	Priya
104	Karan
111	Amit

4 rows in set

Limits and safety

MySQL stops after cte_max_recursion_depth rounds (default 1,000) with an error.
Always have a condition that ends the recursion (WHERE n < 10, or data that ends, like the bottom of a tree).
If the data could contain a loop (A reports to B, B reports to A), track the path and stop when an id is already in it.

MySQL

WITH RECURSIVE forever AS (  SELECT 1 AS n  UNION ALL  SELECT n + 1 FROM forever)SELECT COUNT(*) FROM forever;

ResultMySQL 8.4

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Exercise: Powers of two

Generate the powers of two from 1 to 1024 (1, 2, 4, … 1024).

Expected resultMySQL 8.4

n
1
2
4
8
16
32
64
128
256
512
1024

11 rows in set

Hint 1

Anchor 1; recursive part n * 2 while n < 1024.

SQL

WITH RECURSIVE powers AS (  SELECT 1 AS n  UNION ALL  SELECT n * 2 FROM powers WHERE n < 1024)SELECT n FROM powers;

ResultMySQL 8.4

n
1
2
4
8
16
32
64
128
256
512
1024

11 rows in set

Quick quiz

1. What are the two parts of a recursive CTE?

2. When does the recursion stop?

3. Why cast the path column in the anchor to CHAR(200)?

Recursive CTE FAQ

What is a recursive CTE?

A CTE that references itself. It combines a starting query with a query that repeatedly builds new rows from the previous ones until no more rows are produced.

How do I query a hierarchy in MySQL?

Use WITH RECURSIVE: start from the top (or a chosen node) and join the table to the CTE on the parent column, adding a level or path column as you go.

What is the maximum recursion depth in MySQL?

cte_max_recursion_depth defaults to 1,000. You can raise it for a session with SET SESSION cte_max_recursion_depth = 5000;.