Recursive CTEs

Most SQL you write is flat: you query a table, maybe join another, and get rows back. But some data is not flat. An employee reports to a manager who reports to another manager. A task depends on subtasks that have their own dependencies. A category contains subcategories.

Before recursive CTEs, handling that kind of depth in SQL meant either knowing the tree's depth in advance (one self-join per level, fragile and brittle) or pulling everything into application code and walking it there. Recursive CTEs let you traverse arbitrary depth in pure SQL. The surprising part is how the mechanism works - it is not like any other query.

The structure

Every recursive CTE has two parts, connected by UNION ALL:

WITH RECURSIVE cte_name AS (
  -- Part 1: the anchor (runs once, produces the starting rows)
  SELECT ...

  UNION ALL

  -- Part 2: the recursive part (joins cte_name to itself)
  SELECT ... FROM source_table JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Think of it like a loop. The anchor produces the starting rows. Then the recursive part looks at what you have so far, finds the next layer of related rows, and adds them. That continues until the recursive part produces nothing new. The final result is every row produced across all iterations combined.

Walking an organizational hierarchy

The classic example is an employee table where each row has a manager_id pointing to another employee. You want everyone in the hierarchy under a given person, regardless of how many levels deep it goes:

WITH RECURSIVE org_tree AS (
  -- Anchor: start with the CEO (has no manager)
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find the direct reports of everyone found so far
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Iteration 0 - the anchor finds the CEO. Iteration 1 - the recursive part finds their direct reports. Iteration 2 - it finds the direct reports of those people. This continues until no new employees are found.

The depth column is worth keeping. It tells you how many levels down each node sits, which is useful for indenting a tree display, filtering to a specific level, or just understanding the structure.

Building the path

You can also carry the full path from root to each node by building it up as you go:

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, ARRAY[name] AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.path || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, array_to_string(path, ' > ') AS full_path
FROM org_tree
ORDER BY full_path;

This gives you rows like CEO > VP Engineering > Alice. Useful for display, for debugging, or for detecting cycles in the path itself.

Handling cycles in graphs

Trees have no cycles, but graphs do. If a recursive CTE hits a cycle, it loops until the database hits a row or depth limit. The defense is to track which nodes you have visited and stop if you see one again:

WITH RECURSIVE graph AS (
  SELECT id, ARRAY[id] AS visited
  FROM nodes
  WHERE id = 1  -- starting node

  UNION ALL

  SELECT n.id, g.visited || n.id
  FROM edges e
  JOIN nodes n ON n.id = e.target
  JOIN graph g ON g.id = e.source
  WHERE NOT n.id = ANY(g.visited)  -- stop if we have been here before
)
SELECT * FROM graph;

PostgreSQL 14 added a CYCLE clause that handles this automatically:

WITH RECURSIVE graph AS (
  SELECT id FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id FROM edges e JOIN nodes n ON n.id = e.target JOIN graph g ON g.id = e.source
) CYCLE id SET is_cycle USING visited_path
SELECT * FROM graph WHERE NOT is_cycle;

When recursive CTEs are the right tool

Recursive CTEs are genuinely complex. Reach for them when the depth is unknown - not as a first instinct for any hierarchical problem. If you know there are two or three levels, a couple of self-joins is simpler and easier to read. The recursive version earns its complexity when the structure could be arbitrarily deep.