SQL Recursive CTE: Hierarchies, Org Charts and Number Series

A self join can find an employee's manager. But what about the manager's manager, and so on to the top, when you do not know how many levels there are? A recursive CTE is a CTE that refers to itself, repeating a step until there is nothing left to find.

How it works#

WITH RECURSIVE cte_name AS (
  -- 1. Anchor: the starting rows
  SELECT ...

  UNION ALL

  -- 2. Recursive part: refers to cte_name, finds the next rows
  SELECT ...
  FROM some_table
  JOIN cte_name ON ...
)
SELECT * FROM cte_name;

The database:

1. Runs the anchor query once.
2. Runs the recursive part using the rows found in the previous step.
3. Repeats step 2 with each new batch of rows, until a step returns no rows.
4. Returns everything found, combined with UNION ALL.

The simplest example: counting#

WITH RECURSIVE numbers(n) AS (
  SELECT 1                 -- anchor: start at 1
  UNION ALL
  SELECT n + 1             -- recursive: add one
  FROM numbers
  WHERE n < 5              -- stop condition
)
SELECT n FROM numbers;

n
-
1
2
3
4
5

The anchor produces 1. Each recursive step takes the previous row and adds one, until n < 5 is no longer true and the step returns nothing.

Generating a date series#

Series are useful for reports that must show every day, even days with no data. Daily revenue for 9–13 February:

-- SQLite date syntax
WITH RECURSIVE days(d) AS (
  SELECT '2026-02-09'
  UNION ALL
  SELECT date(d, '+1 day')
  FROM days
  WHERE d < '2026-02-13'
)
SELECT days.d AS day,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM days
LEFT JOIN orders o ON o.order_date = days.d
GROUP BY days.d
ORDER BY days.d;

day        | revenue
-----------+--------
2026-02-09 | 0
2026-02-10 | 0
2026-02-11 | 120
2026-02-12 | 0
2026-02-13 | 0

PostgreSQL has a built-in shortcut for this: generate_series('2026-02-09'::date, '2026-02-13', '1 day').

Walking an organisation chart#

A larger example, with a staff table where each row points to a manager:

Start at the top (the person with no manager), then repeatedly find everyone who reports to someone already found. Track the depth and the full path as you go:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id,
         0 AS depth,
         name AS path
  FROM staff
  WHERE manager_id IS NULL                -- anchor: the CEO

  UNION ALL

  SELECT s.id, s.name, s.manager_id,
         o.depth + 1,
         o.path || ' > ' || s.name
  FROM staff s
  JOIN org o ON s.manager_id = o.id       -- children of rows found so far
)
SELECT depth, name, path
FROM org
ORDER BY path;

depth | name        | path
------+-------------+-------------------------------------
0     | Anita (CEO) | Anita (CEO)
1     | Bala        | Anita (CEO) > Bala
2     | Deepak      | Anita (CEO) > Bala > Deepak
3     | Farhan      | Anita (CEO) > Bala > Deepak > Farhan
2     | Esha        | Anita (CEO) > Bala > Esha
1     | Chitra      | Anita (CEO) > Chitra

Sorting by path displays the tree in order, with every person under their manager.

Walking up: the chain of command#

Reverse the join to climb up from one person to the top:

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id
  FROM staff
  WHERE name = 'Farhan'                   -- anchor: start here

  UNION ALL

  SELECT s.id, s.name, s.manager_id
  FROM staff s
  JOIN chain c ON s.id = c.manager_id     -- the manager of the previous row
)
SELECT name FROM chain;

name
-----------
Farhan
Deepak
Bala
Anita (CEO)

Preventing infinite loops#

If the data contains a cycle (A manages B, B manages A), the recursion never ends. Protect yourself:

• Add a depth limit: WHERE o.depth < 20 in the recursive part.
• SQL Server stops after 100 levels by default (OPTION (MAXRECURSION n) changes it).
• PostgreSQL 14+ has a CYCLE clause that detects loops automatically.

Common mistakes#

Practice#

WITH RECURSIVE team AS (
  SELECT id, name
  FROM staff
  WHERE name = 'Bala'

  UNION ALL

  SELECT s.id, s.name
  FROM staff s
  JOIN team t ON s.manager_id = t.id
)
SELECT name
FROM team
WHERE name <> 'Bala'
ORDER BY name;

name
------
Deepak
Esha
Farhan

Key takeaway#