Handling Recursive Queries with Common Table Expressions (CTEs) in SQL

Recursive queries in SQL allow you to perform operations that involve hierarchical or sequential data, such as organizational charts or folder structures. A Common Table Expression (CTE) provides a clean way to write these queries by allowing a named temporary result set that refers to itself. In this tutorial, you'll learn what recursive CTEs are and how to use them with clear examples.

A recursive CTE consists of two parts: the anchor member and the recursive member. The anchor member is the base query that runs first, and the recursive member references the CTE itself to process additional rows. The recursion stops when the query returns no new rows.

Let's see a simple example using an employee hierarchy table. Suppose we have a table called Employees with columns EmployeeID, ManagerID, and Name. We want to find the entire chain of employees under a specific manager.

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

INSERT INTO Employees (EmployeeID, ManagerID, Name) VALUES
(1, NULL, 'CEO'),
(2, 1, 'Director'),
(3, 2, 'Manager'),
(4, 3, 'Lead Developer'),
(5, 3, 'QA Lead');

This query finds all employees who report directly or indirectly to the employee with EmployeeID = 2 (the Director):

WITH RecursiveEmployees AS (
    -- Anchor member: start with the manager
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE EmployeeID = 2
    
    UNION ALL
    
    -- Recursive member: find employees reporting to the previous level
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN RecursiveEmployees re ON e.ManagerID = re.EmployeeID
)
SELECT * FROM RecursiveEmployees;

This query works in two steps: first it selects the Director (EmployeeID = 2), then it repeatedly joins the Employees table to find all employees whose ManagerID matches the earlier level's EmployeeID. This process continues until no more employees are found.

Recursive CTEs are powerful for many use cases like traversing hierarchical data, generating sequences, or processing recursive calculations. Remember to always include a stopping condition (like no new rows) to avoid infinite loops.

As a bonus, you can add a "level" column to keep track of the recursion depth, helping you understand the hierarchy levels:

WITH RecursiveEmployees AS (
    SELECT EmployeeID, ManagerID, Name, 0 AS level
    FROM Employees
    WHERE EmployeeID = 2

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, e.Name, re.level + 1
    FROM Employees e
    INNER JOIN RecursiveEmployees re ON e.ManagerID = re.EmployeeID
)
SELECT * FROM RecursiveEmployees ORDER BY level;

Try writing your own recursive queries using CTEs to explore recursive data in your databases!