Recursive CTEs: How SQL Secretly Learned to Loop

#database #programming #tutorial #sql

Ask a SQL query to find "all employees under this manager," and things get ugly fast if you don't know how many levels deep the org chart goes. A regular join handles one level. Two joins handle two levels. Nobody's writing seven joins for seven levels of middle management.

This is exactly the problem recursive CTEs exist to solve — and most people who've written SQL for years have never touched one.

The setup: data that references itself

Picture a plain employees table where each row points to its own manager:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name TEXT,
  manager_id INT REFERENCES employees(id)
);

Simple structure. The pain shows up the moment you ask "give me this manager and everyone below them, however many layers down." A JOIN can walk exactly one level of that relationship. It has no concept of "keep going until there's nothing left."

The recursive CTE

A recursive CTE is a query that references itself, built from two parts glued together with UNION ALL:

WITH RECURSIVE org_chart AS (
  -- Anchor member: where the recursion starts
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 3  -- the manager we're starting from

  UNION ALL

  -- Recursive member: joins back to the CTE itself
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

Here's what's actually happening:

The anchor member runs once and produces the starting row(s) — in this case, the one manager we care about.
The recursive member joins the real table back to the CTE's own results so far, pulling in the next level down.
The database repeats step 2 — feeding each round's output back in as input — until a round produces zero new rows, then stops.

That depth column isn't required, but it's worth keeping. It turns "an unordered pile of employees" into something you can actually order and indent to look like a real org chart.

A second use case: category trees

Org charts are the classic example, but this pattern shows up anywhere data nests: product categories, comment threads, folder structures, bill-of-materials breakdowns. Same shape, same fix:

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name::TEXT AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

That path column builds a breadcrumb trail (Electronics > Laptops > Gaming) for free, just by concatenating as it recurses.

Where this bites people

UNION ALL, not UNION. Swap in plain UNION and the database now has to deduplicate every intermediate round against every other round, which is expensive and usually unnecessary — you already know these are distinct rows by construction.

Cyclic data causes infinite loops. If your hierarchy has a cycle (employee A manages B, B somehow manages A — yes, this happens with bad data), a naive recursive CTE will spin forever or until it hits an engine-specific safety limit. Guard against it by tracking visited IDs in an array and checking before each recursive step, or add a hard depth cap:

WHERE depth < 50

Engine support varies. PostgreSQL, SQL Server, and MySQL 8.0+ all support WITH RECURSIVE. Older MySQL versions (pre-8.0) don't support it at all — you'd be stuck with application-level recursion or a different table design.

It's not free at scale. For very deep or very wide hierarchies queried constantly, a recursive CTE recomputes the walk every single time. If read performance matters more than write simplicity, patterns like closure tables or materialized paths trade some insert/update complexity for much faster reads.

The takeaway

Most SQL work never needs this. But the moment you're modeling anything that nests — org charts, categories, threads, parts lists — a recursive CTE replaces what would otherwise be a loop in application code, a stack of unknown-depth joins, or a recursive function call. It's one of the few places SQL quietly does something genuinely clever.

DEV Community