Recursive CTEs and Hierarchical Data

Query trees, org charts and paths with a query that references itself

Big idea: A recursive Common Table Expression (CTE) is a named query that can refer to itself, letting a single SQL statement walk a hierarchy of unknown depth. You write an anchor member (the starting rows) and a recursive member (rows derived from the previous step), glue them with UNION ALL, and the engine keeps iterating until the recursive member returns nothing. This is how you flatten an org chart, expand a category tree, or trace a path from a node to the root without knowing how many levels exist.

By the end you will be able to:

read the anatomy of WITH RECURSIVE — anchor + recursive member joined by UNION ALL,
walk a parent/child table (org chart, category tree) top-down and bottom-up,
track a depth level and build a path string as you descend,
bound recursion with a termination condition and understand MySQL's depth limit,
spot cycles and know which safeguards each dialect gives you.

Assumes you are comfortable with ordinary (non-recursive) CTEs, joins and subqueries from the earlier courses. Here we only add the self-reference.

1. The example database

Every query below runs against one small company database. The employee table is self-referencing: each row stores the id of that person's manager, and the CEO has NULL. That single manager_id column encodes an entire org chart of arbitrary depth.

CREATE TABLE employee (
    id          INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    manager_id  INT NULL,
    salary      INT NOT NULL,
    FOREIGN KEY (manager_id) REFERENCES employee(id)
);

INSERT INTO employee (id, name, manager_id, salary) VALUES
    (1, 'Alice', NULL, 300000),
    (2, 'Bob',   1,    200000),
    (3, 'Carol', 1,    190000),
    (4, 'Dan',   2,    120000),
    (5, 'Eve',   2,    125000),
    (6, 'Frank', 3,    110000),
    (7, 'Grace', 4,    90000),
    (8, 'Heidi', 4,    92000);

Drawn out, the reporting lines look like this:

Alice (CEO)
  - Bob
      - Dan
          - Grace
          - Heidi
      - Eve
  - Carol
      - Frank

We will also use a second self-referencing table, category, for a product-catalogue tree. It has the same shape: a parent_id that points back into the same table.

CREATE TABLE category (
    id        INT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    parent_id INT NULL
);

INSERT INTO category (id, name, parent_id) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers',   1),
    (3, 'Laptops',     2),
    (4, 'Desktops',    2),
    (5, 'Phones',      1),
    (6, 'Audio',       1),
    (7, 'Headphones',  6);

Recursive CTEs and Hierarchical Data

Recursive CTEs and Hierarchical Data

1. The example database

Continue this lesson on YourAIZone

In the full lesson