After this lesson you can follow a chain of any length, such as "everyone below Meera in the company", with one query. Self joins can only look a fixed number of steps away. A recursive CTE keeps going until the chain ends.
The two parts
A recursive CTE is a WITH query that refers to its own name. It has two parts joined by UNION ALL:
- The anchor query runs once. It returns the starting rows.
- The recursive query refers to the CTE. It takes the rows produced in the previous round and produces the next ones.
The database repeats the second part until it produces no new rows, then returns everything collected. The smallest example counts from 1 to 5:
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+The anchor produces the single row 1. The recursive part reads that row and produces 2, which it reads to produce 3, and so on. When n reaches 5, the WHERE n < 5 test fails, no new row appears, and the process stops. That WHERE is the stopping condition. Without it the query would never finish. Number series like this are useful for building calendars or test data.
Walking down the hierarchy
Now the real use. To find everyone who reports to Meera at any depth, start with Meera and repeatedly add the people whose manager is already in the result:
WITH RECURSIVE org AS (
SELECT id, first_name, manager_id, 0 AS depth
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.first_name, e.manager_id, o.depth + 1
FROM employees e
INNER JOIN org o ON e.manager_id = o.id
)
SELECT id, first_name, depth
FROM org
ORDER BY depth, id;+----+------------+-------+
| id | first_name | depth |
+----+------------+-------+
| 1 | Meera | 0 |
| 2 | Arjun | 1 |
| 3 | Kavya | 1 |
| 4 | Rohan | 2 |
| 5 | Isha | 2 |
| 19 | Diya | 2 |
+----+------------+-------+Round by round: the anchor gives Meera at depth 0. The first recursive round finds employees whose manager_id is 1 (Arjun and Kavya) at depth 1. The next round finds who reports to them: Rohan, Isha and Diya at depth 2. The next round finds nobody new, so it ends. The depth column counts levels and is a handy by-product.
Notice what a plain self join could not do. With one self join you reach Arjun and Kavya. With two you reach Rohan, Isha and Diya. The recursive version needs no knowledge of how many levels exist.
Showing the path
You often want the chain as text, such as "Meera > Arjun > Rohan". Build it by appending in the recursive step. In standard SQL the concatenation operator is ||:
WITH RECURSIVE org AS (
SELECT id, first_name, 0 AS depth, first_name AS path
FROM employees
WHERE manager_id IS NULL AND department_id = 1
UNION ALL
SELECT e.id, e.first_name, o.depth + 1, o.path || ' > ' || e.first_name
FROM employees e
INNER JOIN org o ON e.manager_id = o.id
)
SELECT path, depth
FROM org
ORDER BY path;+-----------------------+-------+
| path | depth |
+-----------------------+-------+
| Meera | 0 |
| Meera > Arjun | 1 |
| Meera > Arjun > Isha | 2 |
| Meera > Arjun > Rohan | 2 |
| Meera > Kavya | 1 |
| Meera > Kavya > Diya | 2 |
+-----------------------+-------+Here the anchor is "the top of Engineering": someone in department 1 with no manager. Each recursive round adds one name to the path. Sorting by the path text puts each person right below their manager, so the output reads like a tree. The concatenation || works in PostgreSQL and SQLite, but MySQL needs CONCAT(...) by default and SQL Server uses + or CONCAT.
Walking up the hierarchy
You can also go in the other direction: from an employee up through their managers to the top. Swap the join condition, so each round finds the manager of the current row:
WITH RECURSIVE chain AS (
SELECT id, first_name, manager_id, 0 AS steps_up
FROM employees
WHERE id = 19
UNION ALL
SELECT m.id, m.first_name, m.manager_id, c.steps_up + 1
FROM employees m
INNER JOIN chain c ON c.manager_id = m.id
)
SELECT first_name, steps_up
FROM chain
ORDER BY steps_up;+------------+----------+
| first_name | steps_up |
+------------+----------+
| Diya | 0 |
| Kavya | 1 |
| Meera | 2 |
+------------+----------+Diya reports to Kavya, who reports to Meera, who has no manager. The last round finds nothing because Meera's manager_id is NULL, and NULL matches no row. The chain is Diya, Kavya, Meera.
Summarising a whole subtree
Combine the walk-down with a final aggregate to answer "how many people are under this person, at any depth?" Here, for the Engineering director:
WITH RECURSIVE org AS (
SELECT id, manager_id FROM employees WHERE manager_id = 1
UNION ALL
SELECT e.id, e.manager_id
FROM employees e
INNER JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) AS people_below_meera FROM org;+--------------------+
| people_below_meera |
+--------------------+
| 5 |
+--------------------+Arjun and Kavya report directly to Meera, Rohan and Isha report to Arjun, and Diya reports to Kavya. That is five people below her, even though only two report to her directly.
Stopping runaway recursion
If the data contains a loop, say A is B's manager and B is A's manager, the recursion would never reach a round with no new rows. Databases protect you in different ways, but you should protect yourself too, by carrying a depth counter and capping it:
WITH RECURSIVE org AS (
SELECT id, first_name, 0 AS depth FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.first_name, o.depth + 1
FROM employees e
INNER JOIN org o ON e.manager_id = o.id
WHERE o.depth < 10
)
SELECT MAX(depth) AS levels FROM org;+--------+
| levels |
+--------+
| 2 |
+--------+The WHERE o.depth < 10 guarantees an end after ten rounds even if the data were circular. In this company the real answer is 2 levels below Meera, found well before the limit.
Differences between databases
- The RECURSIVE keyword. MySQL, PostgreSQL and SQLite require
WITH RECURSIVE. SQL Server does not use the wordRECURSIVE: you write plainWITHand the recursion is recognised from the structure. - Text joining.
||in PostgreSQL and SQLite,CONCATin MySQL,+orCONCATin SQL Server. - Limits. MySQL stops a recursive CTE after a configurable number of rounds (the setting is
cte_max_recursion_depth), and SQL Server stops at 100 by default unless you add aMAXRECURSIONoption. PostgreSQL does not stop a loop on its own. Check the limits in your database's documentation. - Version. MySQL needs 8.0 or newer. For more, see Common table expressions in MySQL.
Common mistakes
- Forgetting UNION ALL between the parts. Both queries must also return the same columns in the same order.
- No way to stop. Make sure the join can run out of rows, or add a depth limit.
- Using the wrong join direction.
e.manager_id = o.idwalks down.c.manager_id = m.idwalks up. - Starting from the wrong rows. The anchor decides who the tree starts from. Anchoring on a department's top person is different from anchoring on every manager-less person.
- Treating it as the first tool to reach for. For one or two known levels, plain self joins are simpler.
Exercise
Show all employees below Vikram Singh (employee 6) at any depth, with their depth below him. Then show the management chain above Tara Menon (employee 9) up to the top, with the number of steps up.
Show one possible answer
WITH RECURSIVE below AS (
SELECT id, first_name, 0 AS depth FROM employees WHERE id = 6
UNION ALL
SELECT e.id, e.first_name, b.depth + 1
FROM employees e
INNER JOIN below b ON e.manager_id = b.id
)
SELECT first_name, depth FROM below ORDER BY depth, first_name;
WITH RECURSIVE above AS (
SELECT id, first_name, manager_id, 0 AS steps_up FROM employees WHERE id = 9
UNION ALL
SELECT m.id, m.first_name, m.manager_id, a.steps_up + 1
FROM employees m
INNER JOIN above a ON a.manager_id = m.id
)
SELECT first_name, steps_up FROM above ORDER BY steps_up;+------------+-------+
| first_name | depth |
+------------+-------+
| Vikram | 0 |
| Neha | 1 |
| Sameer | 1 |
| Aman | 2 |
| Tara | 2 |
+------------+-------+
+------------+----------+
| first_name | steps_up |
+------------+----------+
| Tara | 0 |
| Neha | 1 |
| Vikram | 2 |
+------------+----------+Key points
- A recursive CTE has an anchor query that gives the starting rows and a recursive query that refers to the CTE itself, joined with UNION ALL.
- It repeats the recursive step on the rows from the previous round until a round returns no new rows.
- Use it for trees of unknown depth, such as an org chart or a category tree, and for generating number series.
- Always make sure the recursion can end. A cycle in the data, or a missing condition, causes an endless loop, so add a depth limit as a safety net.
- The keyword RECURSIVE is needed in MySQL, PostgreSQL and SQLite but not in SQL Server.