Recursive CTE explained — org charts, trees, and when you actually need one

A recursive CTE is a query with three moving parts: an anchor (where to start), a step (how to grow), and a stop (no new rows → done). That's it. If you can say those three sentences about your problem — "start at Bob, follow manager_id upward, stop at the CEO" — you can write the query. This post walks a real 50-person org chart in both directions.

The shape: anchor + step + stop

WITH RECURSIVE walk AS (
  -- 1. ANCHOR: the starting row(s). Runs once.
  SELECT ... FROM t WHERE <start condition>

  UNION ALL

  -- 2. STEP: joins the PREVIOUS round back to the table. Repeats.
  SELECT ... FROM t JOIN walk ON <how rows connect>
)
-- 3. STOP is implicit: a round that adds no rows ends the recursion.
SELECT * FROM walk;

The engine does not "call itself" like a programming-language function. It runs the anchor, then repeatedly runs the step against the rows the previous round produced, accumulating results, until a round comes back empty. It is a loop with a growing pile, not a call stack.

Walking UP: everyone above Bob

SQL Quest's employees table stores hierarchy the standard way: each row carries a manager_id pointing at another row. "Who is above Bob Smith?" has unknown depth — that is the recursion signal.

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employees WHERE name = 'Bob Smith'       -- anchor: start at Bob

  UNION ALL

  SELECT e.emp_id, e.name, e.manager_id, chain.level + 1
  FROM employees e
  JOIN chain ON e.emp_id = chain.manager_id      -- step: my manager's row
)
SELECT name, level FROM chain ORDER BY level;

Three rounds: Bob (anchor) → Bob's manager_id points at Alice → Alice's points at Eva → Eva's manager_id is NULL, the join matches nothing, the round comes back empty, recursion stops. The stop condition is the data itself running out — nobody wrote a loop counter.

Walking DOWN: everyone under Eva

Same skeleton, join flipped: instead of "my manager's row," the step finds "rows whose manager is someone I already found."

WITH RECURSIVE team AS (
  SELECT emp_id, name, 0 AS depth
  FROM employees WHERE name = 'Eva Martinez'

  UNION ALL

  SELECT e.emp_id, e.name, team.depth + 1
  FROM employees e
  JOIN team ON e.manager_id = team.emp_id       -- flipped: who reports to me?
)
SELECT name, depth FROM team ORDER BY depth, name;

On the real data this returns Eva at depth 0, her 10 direct reports at depth 1, and their reports below — the whole subtree, one query, no idea beforehand how deep it went. Compare the two ON clauses: e.emp_id = chain.manager_id walks up, e.manager_id = team.emp_id walks down. The direction of a hierarchy walk lives entirely in that one line.

The other use: generating sequences

Recursion can also manufacture rows from nothing — numbers, dates, time buckets:

WITH RECURSIVE n(x) AS (
  SELECT 1                          -- anchor
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5  -- step + EXPLICIT stop
)
SELECT x FROM n;   -- 1, 2, 3, 4, 5

Note the difference from the org chart: here the data never runs out on its own, so the stop lives in the step's WHERE x < 5. Swap 5 for a date and +1 for date(x, '+1 day') and you have a calendar table — the standard fix for "my report skips days with no sales."

The infinite-loop trap

Two defenses, use at least one on data you don't fully trust:

• Depth cap: you already carry level/depth — add WHERE chain.level < 20 to the step. No real org chart is 20 deep; a runaway walk dies quietly instead of taking a connection with it.
• UNION instead of UNION ALL: UNION deduplicates between rounds, so a revisited row produces nothing new and that branch stops. Costs a dedup pass; terminates cycles. (For trees — which cannot have cycles — stay with UNION ALL, it is faster and the standard.)

When NOT to use recursion

• Fixed, known depth. "Employee and their direct manager" is one self-join. "…and the manager's manager" is two. Recursion for exactly-two levels is ceremony without payoff — see the self-join section of the JOINs guide.
• The engine has a purpose-built tool. Generating dates? PostgreSQL has generate_series() — use it over the manual generator when available.
• You only needed the leaves. Sometimes a WHERE manager_id IS NULL (roots) or an anti-join (leaves) answers the actual question without walking anything.

Rule of thumb: recursion is for unknown depth. Known depth = joins. Unknown depth = WITH RECURSIVE.

▶ Start with the plain WITH — recursion is one UNION ALL away

FAQ

Is a "nested CTE" the same as a recursive CTE?

No. "Nested" usually means several plain CTEs where later ones reference earlier ones — a pipeline, no self-reference, nothing recursive about it. A recursive CTE references itself inside its own definition. Pipelines: CTE tutorial. Self-reference: this post.

Which databases support WITH RECURSIVE?

All the majors. PostgreSQL, MySQL 8+, SQLite and MariaDB require the RECURSIVE keyword; SQL Server and Oracle use plain WITH and detect the self-reference. The skeleton is identical everywhere.

How deep can it go?

Engine-dependent: SQLite defaults to 1,000 iterations, SQL Server to 100 (OPTION (MAXRECURSION n) to change), PostgreSQL and MySQL are bounded by memory/config. If you are hitting these limits on an org chart, check for a cycle before raising them.