A Recursive CTE is a CTE that references itself to repeatedly execute a query until a condition is met.

It is mainly used for hierarchical or tree-structured data, such as:

  • Employee → Manager hierarchy
  • Category → Subcategory
  • Organization structure
  • Folder trees

1.Structure of a Recursive CTE

A recursive CTE has two parts:

1. Anchor Member

Returns the starting rows

2. Recursive Member

References the CTE itself and processes next level rows

Both parts are joined using UNION ALL.

2.Basic Syntax

WITH RecursiveCTE AS
(
-- Anchor member
SELECT columns
FROM table
WHERE conditionUNION ALL

-- Recursive member
SELECT columns
FROM table
JOIN RecursiveCTE
ON condition
)
SELECT *
FROM RecursiveCTE;

3.Example Table (Employee Hierarchy)

EmployeeID Name ManagerID
1 CEO NULL
2 Manager 1
3 Lead 2
4 Developer 3

4.Simple Recursive CTE Example

WITH EmployeeHierarchy AS
(
-- Anchor: Top-level employee
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL UNION ALL

-- Recursive: Find subordinates
SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh
ON e.ManagerID = eh.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

Output

EmployeeID Name ManagerID Level
1 CEO NULL 0
2 Manager 1 1
3 Lead 2 2
4 Developer 3 3

5.Visual Explanation

CEO
└── Manager
└── Lead
└── Developer

6.Recursive CTE with Path (Hierarchy Path)

WITH EmployeePath AS
(
SELECT EmployeeID, Name, ManagerID,
CAST(Name AS VARCHAR(MAX)) AS Path
FROM Employees
WHERE ManagerID IS NULL UNION ALL

SELECT e.EmployeeID, e.Name, e.ManagerID,
ep.Path + ' → ' + e.Name
FROM Employees e
JOIN EmployeePath ep
ON e.ManagerID = ep.EmployeeID
)
SELECT *
FROM EmployeePath;

Output Example

CEO → Manager → Lead → Developer

7.Recursive CTE with MAXRECURSION

SQL Server limits recursion to 100 levels by default.

Increase or remove limit:

OPTION (MAXRECURSION 200);   -- Set limit
OPTION (MAXRECURSION 0);     -- Unlimited (use carefully)

8.Prevent Infinite Loops (Important!)

If data has circular references:

WITH SafeCTE AS
(
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL UNION ALL

SELECT e.EmployeeID, e.Name, e.ManagerID, s.Level + 1
FROM Employees e
JOIN SafeCTE s
ON e.ManagerID = s.EmployeeID
WHERE s.Level < 10
)
SELECT *
FROM SafeCTE;

9.Recursive CTE for Categories

Categories Table

CategoryID CategoryName ParentID
1 Electronics NULL
2 Mobile 1
3 Android 2

Query

WITH CategoryTree AS
(
SELECT CategoryID, CategoryName, ParentID, 0 AS Level
FROM Categories
WHERE ParentID IS NULL UNION ALL

SELECT c.CategoryID, c.CategoryName, c.ParentID, ct.Level + 1
FROM Categories c
JOIN CategoryTree ct
ON c.ParentID = ct.CategoryID
)
SELECT *
FROM CategoryTree;

Categorized in:

SQL Server,