Traverse Hierarchies With A Recursive CTE

Use `WITH RECURSIVE` to repeatedly join a query to its own results, enabling traversal of tree or graph structures.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Walk an employee org chart from CEO down to individual contributors

The anchor selects Alice (the only row with no manager) at depth 0. Each recursive step finds employees whose manager_id matches a row already in org and increments the depth. Bob and Carol are both at depth 1. Dave reports to Bob and is at depth 2. The recursion terminates when no new employees are found.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE employees (id INT, name VARCHAR(50), manager_id INT);

INSERT INTO
  employees (id, name, manager_id)
VALUES
  (1, 'Alice', NULL),
  (2, 'Bob', 1),
  (3, 'Carol', 1),
  (4, 'Dave', 2);

Validated query

SQL

WITH RECURSIVE
  org AS (
    SELECT
      id,
      name,
      0 AS depth
    FROM
      employees
    WHERE
      manager_id IS NULL
    UNION ALL
    SELECT
      e.id,
      e.name,
      o.depth + 1
    FROM
      employees e
      JOIN org o ON e.manager_id = o.id
  )
SELECT
  name,
  depth
FROM
  org
ORDER BY
  depth,
  id;

Expected result

name	depth
Alice	0
Bob	1
Carol	1
Dave	2

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE employees (id INT, name VARCHAR(50), manager_id INT);

INSERT INTO
  employees (id, name, manager_id)
VALUES
  (1, 'Alice', NULL),
  (2, 'Bob', 1),
  (3, 'Carol', 1),
  (4, 'Dave', 2);

Validated query

SQL

WITH
  org AS (
    SELECT
      id,
      name,
      0 AS depth
    FROM
      employees
    WHERE
      manager_id IS NULL
    UNION ALL
    SELECT
      e.id,
      e.name,
      o.depth + 1
    FROM
      employees e
      JOIN org o ON e.manager_id = o.id
  )
SELECT
  name,
  depth
FROM
  org
ORDER BY
  depth,
  id;

Expected result

name	depth
Alice	0
Bob	1
Carol	1
Dave	2

SQL Server omits the RECURSIVE keyword. All other engines use WITH RECURSIVE. The output is identical across all engines.

Useful when

Where this command helps.

walking parent-child hierarchies like folders or org charts
expanding tree data without procedural loops in application code

Explanation

What the command is doing.

A recursive CTE has two parts joined by UNION ALL: an anchor member that selects the starting rows, and a recursive member that joins the CTE back to itself to extend the result one level at a time. The recursion stops when the recursive member returns no new rows. This pattern is used for org charts, bill-of-materials, folder trees, and any data where rows reference other rows in the same table.

Traverse Hierarchies With A Recursive CTE

Walk an employee org chart from CEO down to individual contributors

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.