How do you write a recursive CTE to traverse hierarchic...

The anchor and recursive members have to agree on column shape — same number of columns, compatible types — because they’re combined with UNION ALL, and it’s worth stating explicitly why UNION ALL rather than UNION: deduplication would require comparing every accumulated row against every new row on each recursive step, which is both usually unnecessary (each row in a proper hierarchy traversal is genuinely distinct by construction) and expensive at any real depth.

The mechanics of self-reference are what make this different from any other CTE: inside the recursive member, the CTE’s own name refers specifically to the result of the previous iteration, not the full accumulated result set — JOIN employees e ON e.manager_id = oh.id where oh is the CTE joins each new level’s children to last iteration’s employees, one level deeper each pass, and the database engine keeps re-running that join until a pass produces zero new rows, at which point recursion halts on its own.

The depth counter and path-tracking aren’t just defensive style — they’re the difference between a query that fails safely and one that hangs a connection indefinitely. Real organizational data occasionally contains a data-entry error that creates a cycle (A reports to B, B reports to A), and without an explicit WHERE depth < 50 or equivalent bound, most database engines will happily recurse until they hit a resource limit or time out, rather than detecting the cycle themselves. Building the path as a concatenated string (path || ' > ' || e.name) as you go also doubles as free debugging output and lets you detect a cycle directly by checking whether the current node’s ID already appears in its own accumulated path.