Recursive CTEs
A query that repeats.
Here's one of the cleverest ideas in SQL. ACTE that uses itself, again and again, until it's told to stop. It's called a recursive CTE. It sounds advanced, but let's start with something very simple. Counting to five.

- A CTE that uses itself
- Repeats until it stops
- Start simple: count to 5
Counting to five
With recursive, then the name. Inside, two parts joined by union all. The first part is the starting row, the number one. The second part takes the previous row, and adds one. Where n is less than five tells it when to stop. Run it. One, two, three, four, five.
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;โโโโโ โ n โ โโโโโค โ 1 โ โ 2 โ โ 3 โ โ 4 โ โ 5 โ โโโโโ
Climbing the ladder
Now something useful. Who is above Tom Baker in the company? Start with Tom. Then, repeatedly, find the manager of the last person found. When someone has no manager, there's nothing left to join, and it stops. Run it. Tom, then Marcus, then Helen at the top.
WITH RECURSIVE chain(staff_id, name, manager_id, level) AS (
SELECT staff_id, name, manager_id, 1
FROM staff
WHERE name = 'Tom Baker'
UNION ALL
SELECT s.staff_id, s.name, s.manager_id, chain.level + 1
FROM staff AS s
JOIN chain ON s.staff_id = chain.manager_id
)
SELECT name, level FROM chain;โโโโโโโโโโโโโโฌโโโโโโโโ โ name โ level โ โโโโโโโโโโโโโโผโโโโโโโโค โ Tom Baker โ 1 โ โ Marcus Lee โ 2 โ โ Helen Ward โ 3 โ โโโโโโโโโโโโโโดโโโโโโโโ
Summary
A recursive CTE has a starting part, and a repeating part, joined by union all. It's perfect for hierarchies. Next, using it to fill gaps in dates.
Key takeaways- WITH RECURSIVE
- Start + repeat
- UNION ALL, with a stop