Recursive CTEs

Lesson 21 ended on a limit: a self join goes exactly one level. Two joins reach a manager's manager. Nothing built from joins alone can answer "everyone below this person, however deep", because you would have to know the depth to write the query.

A recursive CTE can.

SQL▶ Run
WITH RECURSIVE chain(emp_id, first_name, manager_id, depth) AS (
  -- anchor: everyone at the top
  SELECT emp_id, first_name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive step: everyone reporting to a row we already have
  SELECT e.emp_id, e.first_name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.emp_id
)
SELECT depth, first_name
FROM chain
ORDER BY depth, first_name;

All ten employees, each labelled with its distance from the top of the tree.

The three parts

1. The anchor — a normal query giving the starting rows. Here, the people with no manager.
2. UNION ALL — joins the anchor to the recursive part.
3. The recursive step — a query that references the CTE by name. It runs repeatedly: each pass sees only the rows the previous pass produced, and stops when a pass produces none.

That last point is what makes it terminate. The first pass finds the two people with no manager; the second finds everyone reporting to those two; the third everyone reporting to them; and so on until a pass returns nothing.

RECURSIVE is required by PostgreSQL, SQLite and MySQL. SQL Server and Oracle omit the keyword — the same query, just WITH.

Generating rows from nothing

The other everyday use is producing a sequence — dates for a calendar table, or numbers to fill gaps in a report:

SQL▶ Run
WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n, n * n AS squared FROM numbers ORDER BY n;

The WHERE n < 10 is the terminating condition, and it is doing real work. Without it the query does not stop — which brings us to the important part.

Runaway recursion

A recursive CTE with no terminating condition, or one walking data with a cycle, will run until something kills it. If A manages B and B manages A, the recursive step keeps finding new rows forever.

Every database gives you a brake, and they are all different:

The portable habit is to carry a depth column, as the first example does, and add WHERE depth < 50 to the recursive step. It costs nothing and turns an infinite query into a wrong-but-finite one, which is a much better failure.

For data that genuinely may contain cycles, PostgreSQL and Oracle have a CYCLE clause; elsewhere you accumulate the visited path in a string and check it.

Reading it as a loop

If the recursion is hard to picture, this is the equivalent in ordinary terms:

result  = anchor query
frontier = result
while frontier is not empty:
    frontier = recursive step, applied to frontier only
    result  += frontier

The key detail people get wrong: the recursive step sees only the previous pass's rows, not the whole accumulated result. That is why depth increments cleanly by one each pass.

UNION instead of UNION ALL deduplicates each pass, which is one way to stop a cycle — at the cost of the deduplication itself.

What else it is used for

• Bill of materials — parts made of parts made of parts.
• Category trees — every subcategory beneath one node.
• Date spines — every day in a range, so days with no data still show a zero.
• Graph reachability — everything connected to a starting node.
• Filling gaps — generating the rows that should exist and left-joining the real data onto them.

Common mistakes

• No terminating condition — the query runs until the server stops it.
• Forgetting RECURSIVE — required on PostgreSQL, SQLite and MySQL.
• UNION where UNION ALL was meant — silently drops legitimate repeated rows.
• Expecting the recursive step to see all accumulated rows — it sees only the last pass.
• Recursing over cyclic data with no guard — carry a depth and bound it.

Interview question

How would you find every employee beneath a given manager, at any depth?

A recursive CTE: anchor on the manager, then repeatedly join employees to the CTE on manager_id = emp_id. Mentioning a depth guard against cycles is what separates a memorised answer from a considered one.

Module complete

You can now nest queries, correlate them, name them, and recurse through a hierarchy. That covers the machinery most SQL work is built from — and it is enough to read and write the queries that come up in interviews.

Where the course goes next

Modules still being written: window functions (ROW_NUMBER, RANK, running totals — the thing that replaces half the correlated subqueries above), indexes and query plans, transactions and isolation, schema design and normalisation, and the graded interview problem set.

The playground on the course page runs everything taught so far, and every example in every lesson above is executed before it ships.