SQL Recursive CTE

A Common Table Expression (CTE) is a powerful feature in SQL that allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. While a standard CTE is useful for defining a simple result set, a Recursive CTE takes this concept to the next level by enabling you to perform hierarchical queries, such as working with organizational charts, bill of materials, or category hierarchies. In SQL Server, Recursive CTEs are commonly used to navigate and manipulate hierarchical data.

Introduction

Here’s an overview of how Recursive CTEs work in SQL Server:

Basic CTE Structure: A CTE begins with the WITH keyword, followed by a name for the CTE and a query definition enclosed in parentheses. The query within the CTE can reference the CTE itself, allowing for recursion.

WITH MyCTE (column1, column2, ...) AS (
    -- Initial query
    SELECT column1, column2, ...
    FROM some_table
    WHERE condition

    UNION ALL

    -- Recursive query
    SELECT column1, column2, ...
    FROM MyCTE
    WHERE recursive_condition
)

MyCTE: The name of the CTE.
column1, column2, …: Columns selected in both the initial and recursive queries.
some_table: The table or tables you are working with.
condition: The condition for the initial query.
recursive_condition: The condition for the recursive query.

Initial Query: The initial query retrieves the base or root rows of the hierarchy. These are the starting points for the recursion.

Recursive Query: The recursive query is executed repeatedly until no more rows match the recursive_condition. It retrieves child rows that are related to the parent rows from the previous iteration.

UNION ALL: The UNION ALL operator combines the results of the initial query and the recursive query. It includes all rows, including duplicates.

Termination Condition: The recursion continues until no rows are returned by the recursive query, ensuring that it doesn’t go on indefinitely.

Example

Here’s a simple example of using a Recursive CTE to navigate an organizational hierarchy in SQL Server:

WITH EmployeeCTE (EmployeeID, ManagerID, EmployeeName, Depth) AS (
    -- Initial query: Select top-level employees (those with no manager)
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Depth
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive query: Select employees reporting to each manager
    SELECT E.EmployeeID, E.ManagerID, E.EmployeeName, CTE.Depth + 1
    FROM Employees AS E
    INNER JOIN EmployeeCTE AS CTE ON E.ManagerID = CTE.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeName, Depth
FROM EmployeeCTE
ORDER BY Depth, EmployeeName;

In this example, the CTE helps create a hierarchy of employees by recursively joining employees with their respective managers until all levels of the organizational structure are accounted for. The result displays each employee’s ID, manager’s ID, name, and the depth in the hierarchy.

Recursive CTEs are a powerful tool for working with hierarchical data in SQL Server, and they simplify the process of querying and managing such structures. They provide an efficient and elegant solution for tasks involving tree-like data relationships.