Recursive CTEs

A recursive CTE refers to itself, repeating until there's nothing new to add. It's how SQL walks hierarchies of unknown depth — org charts, category trees, threaded comments — and generates series of numbers or dates.

Counting to five

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

A recursive CTE has two parts joined by UNION ALL:

1. The anchor (SELECT 1) produces the starting rows.
2. The recursive part reads the rows produced in the previous step and produces new ones. It repeats until it returns no rows — here, when n reaches 5.

Generating a date series

WITH RECURSIVE days(day) AS (
  SELECT '2026-02-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-02-07'
)
SELECT d.day, COUNT(o.id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
GROUP BY d.day;

A calendar of every day, joined to orders, shows days with zero orders that would otherwise be missing. (PostgreSQL has the built-in generate_series for this.)

Walking down a hierarchy

WITH RECURSIVE team(id, name, level) AS (
  SELECT id, first_name, 0
  FROM employees
  WHERE id = 3
  UNION ALL
  SELECT e.id, e.first_name, t.level + 1
  FROM employees AS e
  JOIN team AS t ON e.manager_id = t.id
)
SELECT name, level FROM team;

Start with Mei (the anchor), then repeatedly add employees whose manager is already in the result. This finds everyone under her, however many levels deep.

A full org chart

WITH RECURSIVE chart(id, name, level, path) AS (
  SELECT id, first_name, 0, first_name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, c.level + 1, c.path || ' > ' || e.first_name
  FROM employees AS e
  JOIN chart AS c ON e.manager_id = c.id
)
SELECT substr('············', 1, level * 3) || name AS org_chart, path
FROM chart
ORDER BY path;

Carrying a path column along shows each person's chain of command, and sorting by it groups each team under its manager. Repeating a character level times draws the tree.

Walking up a hierarchy

WITH RECURSIVE chain(id, name, manager_id) AS (
  SELECT id, first_name, manager_id FROM employees WHERE first_name = 'Priya'
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id
  FROM employees AS e
  JOIN chain AS c ON e.id = c.manager_id
)
SELECT name FROM chain;

Reversing the join condition climbs from an employee up to the top — useful for breadcrumbs in category trees.

Dialect notes

• PostgreSQL, MySQL 8+ and SQLite require the RECURSIVE keyword; SQL Server and Oracle don't use it (just WITH).
• SQL Server limits recursion to 100 levels by default (OPTION (MAXRECURSION n) changes it); MySQL's limit is cte_max_recursion_depth, 1000 by default.
• Data with cycles (A manages B, B manages A) recurses forever unless you track visited rows; PostgreSQL 14+ has a CYCLE clause for this.

Try it yourself

Generate the first ten powers of two.