Recursive CTEs

Walk a hierarchy

Lesson 13 of 20 · 12 min · Module 4: Hierarchies, series and JSON

An organisation chart, product categories, or comment threads are trees: each row points to its parent. A recursive CTE follows those links level by level, however deep the tree is.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Write the anchor and recursive parts of a CTE
Carry values such as depth and path down the tree
Know how recursion stops

The idea

WITH RECURSIVE has two parts joined by UNION ALL: an anchor query that selects the starting rows, and a recursive query that joins the CTE to the table to find the next level. It stops when a step finds no new rows.

WITH RECURSIVE countdown(n) AS (
  SELECT 3
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

01Anchor first

The anchor finds where to start: the CEO, whose manager_id is NULL. It runs once.

02Then repeat

The recursive part joins the rows found in the previous step to their direct reports. Each round goes one level deeper, adding 1 to level and appending a name to path. When a round finds nobody, recursion ends.

WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x * 2 FROM n WHERE x < 8)
SELECT GROUP_CONCAT(x) FROM n;

Output

1,2,4,8

03Guard against cycles

If data accidentally contains a loop (A manages B, B manages A), recursion never ends. A condition such as WHERE level < 20 is a cheap safety net.

Your turn

Return every employee’s name, level (0 for the CEO, 1 for people reporting to the CEO, and so on), and path: the names from the CEO down to them, separated by > . Sort by path.

What the checks look for

Shows all seven employees with their level and path

Try it in the editor →

Take this with you

Recursive CTEs walk trees one level at a time.

Go a little further

Count how many people work under each manager, directly or indirectly.