Recursive CTEs

Walk hierarchies and generate sequences.

Queries that call themselves

A WITH RECURSIVE CTE repeats until it produces no new rows — the tool for hierarchies (org charts, category trees, threads) and generated sequences. It has two parts joined by UNION ALL: a base (anchor) row set, and a recursive part that references the CTE:

WITH RECURSIVE nums AS (
  SELECT 1 AS n              -- base
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5   -- recurse
)
SELECT n FROM nums;

For an org chart, the base is the people with no manager, and the recursive part joins each employee to rows already found.

Each pass joins employees to the rows found so far, so the search walks one level deeper every time. The empty pass is the termination condition — a recursive part that can never come back empty never finishes.