Recursive CTE in SQL

A recursive CTE walks a hierarchy or generates a series.

WITH RECURSIVE reports AS (
    SELECT
        id,
        name,
        0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.id,
        e.name,
        r.depth + 1
    FROM employees AS e
    JOIN reports AS r
        ON e.manager_id = r.id
    WHERE r.depth < 5
)
SELECT *
FROM reports;

How it works

1. The first branch is the seed row, the second is the step.
2. UNION ALL joins the seed to each new generation.
3. A depth column and a bound keep the recursion finite.

Keywords and builtins used here

• ALL
• AS
• FROM
• IS
• JOIN
• NULL
• ON
• RECURSIVE
• SELECT
• UNION
• WHERE
• WITH

The run, in numbers

Lines

19

Characters to type

242

Tokens

63

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 42 seconds.

Type this snippet

Step 3 of 3 in Common table expressions, step 6 of 24 in Analytics & reporting.

← Previous Next →