Org charts, category trees, bills of materials, threaded comments, network paths — a huge amount of real data is a hierarchy: rows that point at other rows in the same table (an employee’s manager_id, a category’s parent_id). A plain JOIN can hop one level, but hierarchies are arbitrarily deep, and you don’t know how deep in advance. The SQL tool for “follow the chain as far as it goes” is the recursive CTE.

Recursive CTEs are notoriously hard to picture because they don’t run once — they run in rounds. So step through one. Below is a 7-person org; press Step to run each recursive round and watch the result set grow one level at a time. The anchor (blue) seeds it; each recursive round attaches the next level of reports.

WITH RECURSIVE org AS (
  anchor   SELECT id, name, manager_id, 1 AS level
          FROM employees WHERE manager_id IS NULL
          UNION ALL
  recursive SELECT e.id, e.name, e.manager_id, o.level + 1
          FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Press Step to run one recursive round.

The two halves: anchor and recursive member

Every recursive CTE has the same skeleton, joined by UNION ALL:

WITH RECURSIVE org AS (
  -- anchor: where the recursion starts
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive: references the CTE itself
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

The anchor member runs once and produces the starting rows — here, the CEO with no manager.
The recursive member references the CTE (org) inside its own definition. It runs repeatedly: each round joins the base table to the rows the previous round produced, adding their children.
The database keeps running the recursive member until a round returns no new rows — then it stops. That’s exactly the “Recursion stops ✓” moment in the visualizer.

The level column is a common trick: the anchor sets it to 1, and each recursive round does level + 1, so every row records how deep it sits. Add a path string the same way and you can sort the tree in display order.

It works across databases (with small differences)

PostgreSQL, MySQL 8+, SQLite, MariaDB use WITH RECURSIVE exactly as above.
SQL Server uses the same structure but without the RECURSIVE keyword — just WITH org AS (...); it infers recursion from the self-reference.
Oracle supports the ANSI recursive CTE, and also has its older CONNECT BY PRIOR hierarchical syntax for the same job.

Trees are safe; graphs can loop forever

A tree has one path to each node, so the recursion always terminates. A graph — where a node can be reached more than one way, or where a cycle exists (A reports to B reports to A) — can make the recursive member loop forever, because it never stops finding “new” rows.

Guard against it:

Track the path and refuse to revisit a node. Build the path with delimiters so ids can’t partially match each other — without the slashes, '%' || 1 || '%' also matches id 12, silently pruning valid branches:

-- anchor
SELECT id, name, 1 AS level, '/' || id || '/' AS path
...
-- recursive member
JOIN org o ON e.manager_id = o.id
WHERE o.path NOT LIKE '%/' || e.id || '/%'

Cap the depth: add AND o.level < 100 to the recursive member.
PostgreSQL has a built-in CYCLE clause that does this for you.

Dialect note: || is string concatenation in PostgreSQL, Oracle, and SQLite. MySQL treats || as logical OR by default — use CONCAT('/', id, '/') there (or enable PIPES_AS_CONCAT). SQL Server uses +.

This is the single most important habit when your hierarchy is really a graph — an unguarded recursive CTE on cyclic data is the classic “query that never returns.”

Performance notes

Recursion isn’t free. A few things keep it fast:

Index the join key (manager_id / parent_id) — every round joins on it.
Select only what you need in the recursive member; dragging wide rows through every round is wasteful.
Stop early where you can — a depth cap or a WHERE that prunes branches you don’t care about shrinks the work dramatically.
For hierarchies that are read far more than they change, consider caching the flattened result in a view or a materialized table instead of recomputing the walk on every query.

Frequently asked questions

What is a recursive CTE? A common table expression that references itself. It runs an anchor query once, then repeats a recursive query — each round building on the previous round’s rows — until no new rows appear.

How do I query a hierarchy or org chart in SQL? Use WITH RECURSIVE: anchor on the top rows (WHERE manager_id IS NULL), then in the recursive member join the table to the CTE on child.manager_id = cte.id, adding a level for depth.

Does SQL Server support recursive CTEs? Yes — with the same structure, but you omit the RECURSIVE keyword: WITH cte AS (anchor UNION ALL recursive).

Why does my recursive query run forever? Your data has a cycle (or multiple paths to a node), so the recursion never runs out of “new” rows. Track the path to avoid revisiting nodes, or cap the depth.

What’s the difference between a tree and a graph here? A tree has exactly one parent per node, so recursion terminates naturally. A graph can have cycles and multiple paths, which requires cycle protection.

When the hierarchy outgrows one query

Walking a tree in SQL is elegant until you’re doing it across systems, on a schedule, with cycle protection and caching to manage. ET1 is the visual, asynchronous ETL tool for that stage — model the traversal and the flattening as nodes on a canvas, preview each level, and reuse it instead of re-deriving the recursion in every report.

Keep learning: the joins a recursive CTE repeats, views to save the flattened tree, and window functions for ranking within each level.

Recursive CTEs: Querying Trees, Graphs & Hierarchies in SQL