AI Hints
Homeโ€บSQL Tutorialโ€บPivots, Lists, and Recursion
SQL ยท Lesson 110 of 126

Recursive CTEs

A query that repeats.

โฑ 2 min readโ–ถ Video at 2:34:59๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Recursive CTEs
From the video: Recursive CTEs
  • 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.

query.sqlsql
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;
Output
โ”Œโ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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
Next up: Filling Gaps in Dates โ€” a calendar from nothing