Mastering Recursive CTEs for Hierarchical Data Modeling in SQL

Hierarchical data, such as organizational charts, product categories, or file systems, often require special handling in SQL because of their parent-child relationships. Recursive Common Table Expressions (CTEs) provide a powerful and elegant solution to query this kind of data, enabling you to navigate hierarchy levels easily.

In this tutorial, we'll walk through the basics of recursive CTEs and build a simple example to demonstrate how to retrieve hierarchical data step-by-step.

Let's assume you have a table named Employees representing an organizational hierarchy. Each employee has an ID and a ManagerID that points to their manager (null if top-level).

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT NULL
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Manager', 2),
(5, 'Developer', 4),
(6, 'Accountant', 3);

The goal is to retrieve the entire hierarchy starting from the CEO, showing all employees and their reporting levels.

Here is the recursive CTE to accomplish this:

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level employee(s)
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: select employees reporting to the previous level
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, ManagerID;

Let's break down how this works:

1. **Anchor member:** This part selects the root employee(s) — here, those without a ManagerID. We assign Level = 0 meaning top level. 2. **Recursive member:** This joins the Employees table with the CTE result from the previous step to find direct reports (children) and increases the Level by 1. 3. **The final SELECT:** Fetches the full hierarchy with levels indicating each employee’s distance from the root.

Run this query to see the hierarchy:

EmployeeID | EmployeeName | ManagerID | Level
-----------|--------------|-----------|------
1          | CEO          | NULL      | 0
2          | CTO          | 1         | 1
3          | CFO          | 1         | 1
4          | Dev Manager  | 2         | 2
6          | Accountant   | 3         | 2
5          | Developer    | 4         | 3

This output shows the hierarchy tree clearly, with Level indicating how deep in the structure an employee is.

Using recursive CTEs is not only limited to organizational charts — it works well for folder structures, bill of materials, or any tree-like data in your databases.

To summarize, recursive CTEs: - Have an anchor member for root nodes - Recursively call themselves to traverse hierarchy - Enable easy querying of hierarchical data with level information Practice writing recursive CTEs with your own datasets to master hierarchical data modeling in SQL.