SQL Recursive CTE

A recursive CTE is a CTE that refers to itself, so it can repeat a step until there's nothing left to add. It's the standard way to walk hierarchies (an org chart, category trees, referral chains) and to generate sequences of numbers or dates.

How it works

WITH RECURSIVE cte_name AS (
  -- 1. Anchor: the starting rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: new rows built from the previous step
  SELECT ...
  FROM cte_name
  JOIN ... ON ...
)
SELECT * FROM cte_name;

The database runs the anchor once, then runs the recursive step on the rows it just produced, then again on those rows, and so on until a step returns nothing.

Examples

1. Count from 1 to 5

The simplest possible recursion:

WITH RECURSIVE numbers AS (
  SELECT 1 AS n          -- anchor
  UNION ALL
  SELECT n + 1           -- recursive step
  FROM numbers
  WHERE n < 5            -- stop condition
)
SELECT n FROM numbers;

Result:

(5 rows)

2. The full org chart with levels

Start from the CEO (no manager), then repeatedly add each person's direct reports:

WITH RECURSIVE org AS (
  SELECT employee_id, employee_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL                 -- anchor: the top

  UNION ALL

  SELECT e.employee_id, e.employee_name, e.manager_id, org.level + 1
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id   -- next level down
)
SELECT level, employee_name
FROM org
ORDER BY level, employee_name;

Result:

(12 rows)

A self join only reaches one level; recursion reaches every level, however deep.

3. Reporting chain as a path

Build a readable path for each employee:

WITH RECURSIVE chain AS (
  SELECT employee_id, employee_name,
         employee_name::text AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.employee_name,
         chain.path || ' > ' || e.employee_name
  FROM employees e
  JOIN chain ON e.manager_id = chain.employee_id
)
SELECT employee_name, path
FROM chain
WHERE employee_name IN ('Harish Gowda', 'Deepak Yadav', 'Tanvi Shah');

Result:

(3 rows)

The ::text cast in the anchor lets the path column grow beyond the original column length.

4. Everyone under one manager

All of Neha Joshi's team, direct and indirect:

WITH RECURSIVE team AS (
  SELECT employee_id, employee_name
  FROM employees
  WHERE employee_name = 'Neha Joshi'

  UNION ALL

  SELECT e.employee_id, e.employee_name
  FROM employees e
  JOIN team t ON e.manager_id = t.employee_id
)
SELECT employee_name
FROM team
WHERE employee_name <> 'Neha Joshi';

Result:

(3 rows)

5. Generate a date series

Every day in the first week of 2026, a useful base for daily reports with no gaps:

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS day
  UNION ALL
  SELECT (day + 1)::date
  FROM days
  WHERE day < DATE '2026-01-07'
)
SELECT day FROM days;

Result:

(7 rows)

In PostgreSQL, generate_series does this more simply, but the recursive version works in MySQL 8 and SQLite too.

Common mistakes

• No stop condition. SELECT n + 1 FROM numbers without WHERE n < 5 runs until the database stops it.
• UNION vs UNION ALL. UNION ALL is standard and faster; UNION removes duplicates, which can hide data issues but also stops some cycles.
• Type mismatch between anchor and recursive part, for example a VARCHAR(100) anchor and a longer concatenated string. Cast in the anchor.

Practice question

Count how many people (direct and indirect) report to each of Rajesh Khanna's direct reports.

WITH RECURSIVE tree AS (
  SELECT employee_id, employee_id AS root_id
  FROM employees
  WHERE manager_id = 1               -- Rajesh's direct reports are the roots

  UNION ALL

  SELECT e.employee_id, t.root_id
  FROM employees e
  JOIN tree t ON e.manager_id = t.employee_id
)
SELECT m.employee_name AS leader,
       COUNT(*) - 1 AS people_below
FROM tree t
JOIN employees m ON m.employee_id = t.root_id
GROUP BY m.employee_name
ORDER BY people_below DESC;

Key takeaways

• WITH RECURSIVE = anchor query + UNION ALL + recursive step.
• The recursive step reads from the CTE itself and must eventually return no rows.
• Use it for hierarchies of any depth and for generating sequences.
• Track level or a path column to understand the structure.