Walk a hierarchy with a recursive CTE

A recursive CTE has an anchor (the starting rows) and a recursive step joined back to itself with UNION ALL. The syntax is standard — it works the same in every modern engine.

-- Everyone down the org chart from the top (manager_id IS NULL)
WITH RECURSIVE tree AS (
  -- anchor: the top of the tree
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive step: attach each employee under a row already in `tree`
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree
ORDER BY depth, id;

Build a readable path and guard against cycles:

WITH RECURSIVE tree AS (
  SELECT id, name, CAST(name AS VARCHAR(1000)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, path FROM tree;

Dialect notes. WITH RECURSIVE works in MySQL 8+, PostgreSQL, SQLite, DuckDB, BigQuery and Snowflake (Snowflake and Oracle also offer CONNECT BY). String concatenation for the path is || everywhere except MySQL, which needs CONCAT(t.path, ' > ', c.name). If the data can contain cycles, carry a visited-path and add WHERE position(c.id::text in path) = 0 (or similar) so the query terminates.