Recursive CTEs — SQL Advanced Module 4 | LeadAfrik Public Economics Hub

Recursive CTEs are SQL's answer to recursion. They're how you walk a tree, traverse a graph, or generate a series of values without writing a loop. Once you internalise the pattern — anchor + recursive step + UNION ALL — they stop being intimidating.

The recursive CTE pattern

sql

WITH RECURSIVE name_of_cte AS (
  -- Anchor query: the base case, runs once
  SELECT … FROM …
  WHERE base_condition

  UNION ALL

  -- Recursive step: references name_of_cte, runs repeatedly until it returns no rows
  SELECT … FROM table
  JOIN name_of_cte ON …
  WHERE termination_condition
)
SELECT * FROM name_of_cte;

Anchor: the base case (e.g., root nodes of a tree, the starting integer)
Recursive step: derives the next layer from the previous one
UNION ALL between them: keeps duplicates and is faster — UNION would dedupe at each step
Termination: the recursion stops when the recursive step returns zero new rows

Use case 1: org-chart hierarchy

sql

-- employees(id, name, manager_id) — manager_id is NULL for the CEO
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL                      -- anchor: CEO

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.level + 1, o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.id             -- recursive: people reporting to someone in 'org'
)
SELECT level, path FROM org ORDER BY path;

The level column emerges naturally: anchor is level 0, each recursive step increments. The path column gives you the full breadcrumb. Sort by path → tree-shaped output.

Use case 2: generate a date series

sql

-- For engines without generate_series (older MySQL etc.)
WITH RECURSIVE date_series AS (
  SELECT DATE '2025-01-01' AS d
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM date_series
  WHERE d < DATE '2025-12-31'
)
SELECT * FROM date_series;

Use case 3: graph traversal with cycle detection

sql

-- friendships(user_a, user_b) — undirected, no cycles guaranteed
WITH RECURSIVE reachable AS (
  SELECT user_b AS friend, 1 AS depth, ARRAY[123] AS visited
  FROM friendships
  WHERE user_a = 123                            -- start from user 123

  UNION ALL

  SELECT f.user_b, r.depth + 1, r.visited || f.user_a
  FROM friendships f
  JOIN reachable r ON f.user_a = r.friend
  WHERE NOT f.user_b = ANY(r.visited)           -- cycle prevention
    AND r.depth < 5                             -- depth cap
)
SELECT DISTINCT friend, MIN(depth) FROM reachable GROUP BY friend ORDER BY MIN(depth);

Always include termination + cycle prevention

A recursive CTE without termination is an infinite loop and will eat memory. The depth cap (depth < N) is your safety. Cycle prevention via a 'visited' array prevents revisiting nodes you've already seen. Both belong in every graph-traversal query.

Performance considerations

Recursive CTEs are evaluated iteratively — each step materialises its result and feeds the next step
Wide intermediate results blow up memory; consider whether you can prune columns in the recursive step
Postgres ≥14 has WITH RECURSIVE … CYCLE syntax for cycle detection without manual array tracking
For very deep recursion, consider whether the problem really needs recursion or whether a self-join with a depth cap (LEFT JOIN to N levels) is enough

Exercise

You have a categories(id, name, parent_id) table — a category tree. Write a query that, given a specific category_id, returns the full ancestor chain (the category itself, its parent, grandparent, etc., up to the root) with depth.