SQL Query for Hierarchical Data with Recursive CTE

Org charts, category trees, bill-of-materials, and comment threads all share the same shape: rows that reference a parent row in the same table. A recursive CTE, written with WITH RECURSIVE, is the standard SQL tool for walking that structure without knowing how many levels deep it goes. This guide covers the anchor and recursive member pattern with a runnable employee-manager example and a category tree example.

The Anatomy of a Recursive CTE

A recursive CTE has two parts joined by UNION ALL: an anchor member that selects the starting row(s), and a recursive member that references the CTE's own name to fetch the next level. SQL repeatedly executes the recursive member, feeding it the rows produced in the previous iteration, until it returns zero rows.

WITH RECURSIVE cte_name AS (
  -- Anchor member: starting point
  SELECT ... FROM some_table WHERE <base condition>

  UNION ALL

  -- Recursive member: joins back to cte_name
  SELECT ... FROM some_table t
  JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

MySQL 8.0+, PostgreSQL, SQL Server, and SQLite (3.8.3+) all support this syntax with WITH RECURSIVE. SQL Server uses WITH cte_name AS (...) without the RECURSIVE keyword, but the anchor/recursive structure is identical.

Example 1: Employee-Manager Org Chart

Consider an employees table where each row stores a reference to its own manager:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT NULL,
  title VARCHAR(100)
);
-- manager_id references employees.id; NULL means top of the org (CEO)

To find every employee who reports up to a specific manager, directly or indirectly:

WITH RECURSIVE org_chart AS (
  -- Anchor: start from the given manager
  SELECT id, name, manager_id, title, 0 AS depth
  FROM employees
  WHERE id = 101   -- the manager we're starting from

  UNION ALL

  -- Recursive: find direct reports of everyone already in org_chart
  SELECT e.id, e.name, e.manager_id, e.title, oc.depth + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, title, depth
FROM org_chart
ORDER BY depth, name;

The depth column tracks how many levels below the starting manager each row is — 0 for the manager, 1 for direct reports, 2 for their reports, and so on. You can use it to indent results in your application layer for a visual tree.

To find the reverse — an employee's full management chain up to the CEO — flip the join direction:

WITH RECURSIVE management_chain AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 245   -- the employee we're starting from

  UNION ALL

  SELECT e.id, e.name, e.manager_id, mc.depth + 1
  FROM employees e
  INNER JOIN management_chain mc ON e.id = mc.manager_id
)
SELECT id, name, depth
FROM management_chain
ORDER BY depth;

Example 2: Category Tree with Full Path

E-commerce and CMS platforms often store categories as a self-referencing tree — for example, Electronics > Computers > Laptops > Gaming Laptops. Given:

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  parent_id INT NULL
);

This query builds the full breadcrumb path for every category in one pass, using string concatenation to accumulate the path at each recursion step:

WITH RECURSIVE category_tree AS (
  -- Anchor: top-level categories with no parent
  SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(500)) AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: append child category name to the parent's path
  SELECT c.id, c.name, c.parent_id, ct.depth + 1,
         CONCAT(ct.full_path, ' > ', c.name)
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, depth, full_path
FROM category_tree
ORDER BY full_path;

In PostgreSQL, replace CAST(name AS CHAR(500)) with name::TEXT and CONCAT() works the same way. The explicit cast in the anchor member matters — without it, some databases infer a narrow column type from the anchor and then truncate longer concatenated paths in later recursion steps.

Preventing Infinite Loops

Recursion normally terminates when the recursive member returns no new rows. But real data sometimes has cycles — a data entry error where employee A reports to B, and B's record was accidentally updated to report to A. Without a safeguard, this loops forever until the database hits a resource limit. Two ways to protect against it:

1. Add a depth counter and cap it: WHERE depth < 20 in the recursive member's join condition, which bounds the maximum recursion depth regardless of the data.
2. In MySQL 8.0.14+ and PostgreSQL 14+, use the standard CYCLE clause to detect and stop revisiting the same row automatically.

-- Depth-capped version (works everywhere)
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 101

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
  WHERE oc.depth < 20   -- safety cap
)
SELECT * FROM org_chart;

Most database engines also enforce a default recursion limit (MySQL's cte_max_recursion_depth, defaulting to 1000) as a last line of defense, but you should not rely on hitting that limit — add your own guard for predictable behavior.

When to Use a Recursive CTE vs Alternatives

• Recursive CTE — best when tree depth is unknown or variable, and you're on MySQL 8+, PostgreSQL, SQL Server, or SQLite
• Adjacency list + application-side recursion — simpler for small trees fully loaded into memory, but does more round trips
• Nested set model or materialized path column — faster reads for trees that change infrequently, at the cost of more complex writes
• Closure table — a separate table storing every ancestor-descendant pair, ideal when you need very fast "all descendants" queries at scale

Frequently Asked Questions