Recursive CTE in SQL

🔁 A Recursive Common Table Expression (Recursive CTE) is a special type of Common Table Expression (CTE) that references itself. It is used to process hierarchical or tree-structured data, such as employee-manager relationships, organizational charts, category trees, file systems, and family trees.

📖 What is a Recursive CTE?

A Recursive CTE repeatedly executes itself until a termination condition is met. It consists of two parts:

  1. Anchor Member – Returns the initial result set.
  2. Recursive Member – References the CTE itself and continues retrieving additional rows.

Information

The recursion automatically stops when the recursive member returns no new rows or when the database reaches its recursion limit.

đŸŽ¯ Why Use Recursive CTEs?

Recursive CTEs simplify queries involving hierarchical relationships without requiring repeated self-joins or procedural loops.

  • 📌 Build organizational hierarchies.
  • 📌 Traverse parent-child relationships.
  • 📌 Display folder or category structures.
  • 📌 Generate sequences and numbers.
  • 📌 Explore graph-like data structures.

📝 Basic Syntax

Recursive CTE Syntax

WITH RECURSIVE cte_name AS
(
    -- Anchor Member
    SELECT ...

    UNION ALL

    -- Recursive Member
    SELECT ...
    FROM table_name
    JOIN cte_name
      ON ...
)
SELECT *
FROM cte_name;

Important

In databases such as PostgreSQL, SQLite, and MySQL 8.0+, the RECURSIVE keyword is required. SQL Server automatically detects recursion when a CTE references itself and therefore does not use the RECURSIVE keyword.

📊 Sample Table

Consider the following Employees table:

EmployeeIDEmployeeNameManagerIDDepartment
1JohnNULLManagement
2Alice1IT
3Bob1Finance
4David2IT
5Emma2IT
6Frank4IT

💡 Basic Recursive CTE Example

Display the complete employee hierarchy starting from the top-level manager.

Employee Hierarchy

WITH RECURSIVE EmployeeHierarchy AS
(
    -- Anchor Member
    SELECT EmployeeID,
           EmployeeName,
           ManagerID,
           1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive Member
    SELECT e.EmployeeID,
           e.EmployeeName,
           e.ManagerID,
           h.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy h
      ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy
ORDER BY Level,
         EmployeeID;

Result:

EmployeeNameManagerIDLevel
JohnNULL1
Alice12
Bob12
David23
Emma23
Frank44

🔍 Understanding the Execution

  1. The anchor member selects the root employee ( John).
  2. The recursive member finds employees managed by John.
  3. The recursion continues by finding employees managed by Alice, Bob, and others.
  4. The process ends when no additional employees are found.

📈 Generating a Sequence of Numbers

Recursive CTEs can generate simple sequences without requiring a numbers table.

Generate Numbers from 1 to 10

WITH RECURSIVE Numbers AS
(
    SELECT 1 AS Number

    UNION ALL

    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT *
FROM Numbers;

đŸŒŗ Recursive CTE for Categories

Recursive CTEs are useful for displaying category hierarchies.

Category Hierarchy

WITH RECURSIVE CategoryTree AS
(
    SELECT CategoryID,
           CategoryName,
           ParentCategoryID
    FROM Categories
    WHERE ParentCategoryID IS NULL

    UNION ALL

    SELECT c.CategoryID,
           c.CategoryName,
           c.ParentCategoryID
    FROM Categories c
    JOIN CategoryTree ct
      ON c.ParentCategoryID = ct.CategoryID
)
SELECT *
FROM CategoryTree;

📊 Common Uses of Recursive CTEs

Use CaseDescription
Employee HierarchiesDisplay managers and subordinates.
Category TreesNavigate parent-child categories.
Folder StructuresDisplay nested directories.
Family TreesRepresent ancestor-descendant relationships.
Number GenerationCreate sequences for calculations and reports.

⚖ī¸ Recursive CTE vs Standard CTE

FeatureStandard CTERecursive CTE
Self-Reference❌ No✅ Yes
Hierarchy Support❌ No✅ Yes
Sequence Generation❌ No✅ Yes
Typical UseOrganize complex queries.Traverse recursive relationships.

đŸ’ŧ Real-World Example

A company wants to display its complete organizational structure, showing each employee's reporting level from the CEO down to junior staff.

Organization Hierarchy Report

WITH RECURSIVE EmployeeHierarchy AS
(
    SELECT EmployeeID,
           EmployeeName,
           ManagerID,
           1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID,
           e.EmployeeName,
           e.ManagerID,
           h.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy h
      ON e.ManagerID = h.EmployeeID
)
SELECT EmployeeName,
       Level
FROM EmployeeHierarchy
ORDER BY Level,
         EmployeeName;

This query produces a clear hierarchy showing every employee's position within the organization.

⚠ī¸ Common Mistakes

  • ❌ Forgetting to include an anchor member.
  • ❌ Omitting a termination condition, causing infinite recursion.
  • ❌ Using UNION instead of UNION ALL, which can unnecessarily remove duplicate rows and reduce performance.
  • ❌ Creating circular parent-child relationships that cause recursion loops.

Warning

Always ensure that recursive queries have a valid stopping condition. Some database systems also enforce a maximum recursion depth to prevent infinite loops.

⚠ī¸ Best Practices

Best Practice

Use descriptive CTE names, define a clear anchor member, always include a proper termination condition, validate hierarchical data to avoid circular references, and test recursive queries with small datasets before executing them on large tables.

🚀 Key Points to Remember

  • 📌 A Recursive CTE references itself.
  • 📌 It consists of an anchor member and a recursive member.
  • 📌 It is ideal for hierarchical and tree-structured data.
  • 📌 UNION ALL is commonly used to combine the anchor and recursive members.
  • 📌 The recursion stops when no additional rows are returned or when the recursion limit is reached.
  • 📌 Recursive CTEs can also generate number sequences and traverse graphs.
>>"Recursive CTEs enable SQL to navigate hierarchies elegantly, one level at a time."

Summary

✅ Recursive CTEs are one of SQL's most advanced and powerful features. They make it possible to process hierarchical data, generate sequences, and solve recursive problems using clear, maintainable SQL instead of complex procedural code or repeated self-joins.