Recursive CTEs

Recursive CTEs: General Explanation and Example

📘 General Explanation: Recursive CTEs

A Recursive CTE is a Common Table Expression that refers to itself.
It’s often used when working with hierarchical or tree-like data — for example:

• organizational charts (employees → managers),
• product categories (main category → subcategory),
• family trees (parent → child),
• file systems (directory → subdirectory).

A recursive CTE has two parts:

1. Anchor member – the starting point (e.g., the root node, the top-level manager).
2. Recursive member – a query that refers back to the CTE itself, to fetch the “next level” of data.

SQL runs these in a loop:

• It first executes the anchor member.
• Then repeatedly applies the recursive member to the results, building the next level each time.
• It stops when no new rows are returned.

This way, we can walk down a hierarchy of unknown depth, without needing to know in advance how many levels there are.

---

🌳 Generalized Example: Employee Hierarchy

Imagine a simple table of employees:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT
);

emp_id	emp_name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	2
4	David	2
5	Eve	3

Here:

• Alice is the CEO (no manager).
• Bob reports to Alice.
• Carol and David report to Bob.
• Eve reports to Carol.

---

Recursive CTE to show hierarchy

WITH RECURSIVE org_chart AS (
    -- Anchor: start with the CEO (no manager)
    SELECT emp_id, emp_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find employees whose manager is in the previous level
    SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT emp_id, emp_name, manager_id, level
FROM org_chart
ORDER BY level, emp_name;

---

✅ Output

emp_id	emp_name	manager_id	level
1	Alice	NULL	0
2	Bob	1	1
3	Carol	2	2
4	David	2	2
5	Eve	3	3

---

🔑 Key Takeaways

• Recursive CTEs let you walk through unknown-depth hierarchies.
• You define a starting point (anchor) and a recursion rule (recursive member).
• Useful for tree structures: org charts, categories, directories, etc.