Return the ID, name, and step number for every person in the chain. Employee `id = 28` is at step `1`, their direct manager at step `2`, and so on up to the CEO

The shape

The recursion walks upward from a single employee to the CEO. The anchor seeds the chain at employee id = 28, and the recursive member joins each subsequent row by matching the previous row's manager_id to the new row's id. This reverses the usual top-down direction: each pass adds the parent of the row already in the CTE rather than the child.

Clause by clause

• The anchor seeds the chain at the target employee:

SELECT id, name, manager_id, 1 AS step
FROM employees
WHERE id = 28

Uri Vance enters chain at step = 1. The row carries his manager_id along with his own id because the recursion needs both to walk upward.

• The recursive member adds each manager in the chain:

UNION ALL
SELECT e.id, e.name, e.manager_id, c.step + 1
FROM employees e
JOIN chain c ON e.id = c.manager_id

The join condition e.id = c.manager_id is the reversed link: the new row's id has to equal the source row's manager_id. Pass one finds Anna Kim, whose id matches Uri's manager_id. Pass two finds Marcus Reid, whose id matches Anna's. Pass three finds Sarah Chen, whose id matches Marcus's. Pass four finds no row whose id matches Sarah's manager_id, because Sarah's manager_id is NULL; the join can't match NULL to anything, and the recursion stops.

• The final SELECT returns the chain:

SELECT id, name, step
FROM chain

Four rows, one per chain member, each carrying the step number that records how far up from Uri they sit.

Why this and not a downward walk filtered to the path

A downward walk would seed the anchor with the CEO and recurse all the way to every leaf, then filter the result to keep only the rows on Uri's path. That works, but it has to materialise every employee in the company just to discard all but four of them. Walking upward from the target visits exactly four rows — the anchor and the three managers — and never touches anyone else. When the question is "what is the chain above this one node," the upward walk is the shape that matches the question.

The trap

The CEO's row terminates the recursion specifically because their manager_id is NULL, and a join on e.id = c.manager_id can never match a NULL. That implicit termination is fine on a hierarchy where the root is the only NULL-managered employee. On data where multiple employees have a NULL manager_id — for example, contractors who report to no one — the upward walk from a non-CEO would still terminate at their first NULL-managered ancestor, which may or may not be the intended stopping point. A WHERE c.manager_id IS NOT NULL inside the recursive member's filter makes the termination rule explicit instead of implicit; both spellings work here, but the explicit form is more robust if the data could carry multiple roots.