SQL: a recursive CTE walks a tree

WITH RECURSIVE tree AS (
    SELECT id, name, manager_id, 1 AS depth
      FROM employee WHERE id = 1              -- base
  UNION ALL
    SELECT e.id, e.name, e.manager_id, t.depth + 1
      FROM employee e JOIN tree t ON e.manager_id = t.id   -- step
     WHERE t.depth < 10                       -- guard
)
SELECT * FROM tree ORDER BY depth;

id | name  | manager_id | depth
 1 | Ann   |       NULL |     1
 4 | Bob   |          1 |     2
 9 | Carl  |          4 |     3

-- The depth guard is not optional in production: a cycle in the data makes
-- the recursion run until the server runs out of memory. UNION instead of
-- UNION ALL also stops cycles, at the cost of deduplicating every step.