Lesson 22 — Recursive hierarchy (expert)

Walking a hierarchy with a recursive CTE

Lesson 15 found one level (each employee's manager). To get everyone under a manager, at any depth, use a recursive CTE: the anchor selects the starting person, and the recursive step joins staff whose manager_id is someone already found.

WITH RECURSIVE tree(id, label) AS (
  SELECT id, label FROM folders WHERE id = 1
  UNION ALL
  SELECT f.id, f.label FROM folders f JOIN tree t ON f.parent_id = t.id
) SELECT label FROM tree

Why it matters: "all subfolders", "all replies in a thread", "everyone in my department" are recursive questions.