How RECURSIVE CTE works in SQL? Best RECURSIVE CTE examples

When working with SQL, one of the most powerful and often underutilized features is the recursive Common Table Expression (CTE). If you’ve ever dealt with hierarchical data like organizational charts or file systems, you know how tricky it can be to query such structures efficiently. That’s where recursive CTEs come into play. In this article, I’ll break down how recursive CTEs work in SQL and provide some of the best practical examples.

What Is a Recursive CTE?

A recursive CTE (Common Table Expression) is a type of CTE that refers to itself, allowing us to perform operations like traversing hierarchical structures. It consists of two key parts:

• Anchor query – This is the base query that provides the initial result set.
• Recursive query – This part refers to the CTE itself and repeatedly processes data until a condition is met.

Recursive CTEs are commonly used to handle hierarchical or tree-like structures, such as:

• Organizational hierarchies
• Category-subcategory structures
• Graphs and network relationships

Basic Syntax of a Recursive CTE

The structure of a recursive CTE typically looks like this:

WITH RecursiveCTE AS (
    -- Anchor Query
    SELECT column1, column2
    FROM table_name
    WHERE base_condition
    
    UNION ALL
    
    -- Recursive Query
    SELECT t.column1, t.column2
    FROM table_name AS t
    JOIN RecursiveCTE AS r ON t.foreign_key = r.primary_key
)
SELECT * FROM RecursiveCTE;

Let’s break down what happens here:

1. The anchor query runs first and retrieves the initial dataset.
2. The recursive query repeatedly calls the CTE using a self-join.
3. The recursion stops when no new rows are returned.

Best RECURSIVE CTE Examples

1. Organizational Hierarchy Example

Imagine we have an employees table storing hierarchical employee-manager relationships:

EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

We want to return all employees along with their hierarchical levels:

WITH EmployeeHierarchy AS (
    -- Anchor Query: Start with the top-level manager
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive Query: Join employees with their subordinates
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

This query retrieves employees in a structured hierarchy along with their levels within the organization.

2. Category and Subcategory Example

Let’s say we have a category table where each category references its parent:

WITH CategoryHierarchy AS (
    -- Anchor Query: Start with top-level categories
    SELECT CategoryID, CategoryName, ParentCategoryID, 1 AS Level
    FROM Categories
    WHERE ParentCategoryID IS NULL

    UNION ALL

    -- Recursive Query: Find subcategories
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, ch.Level + 1
    FROM Categories c
    JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)
SELECT * FROM CategoryHierarchy;

This helps in retrieving meaningful category structures, such as:

• Electronics (Level 1)
  • Computers (Level 2)
  • Smartphones (Level 2)

Preventing Infinite Loops in Recursive CTE

One major challenge when using recursive CTEs is the risk of infinite loops. To prevent this, make sure to:

• Set a recursion depth limit using OPTION (MAXRECURSION n) in SQL Server.
• Ensure that the recursive condition eventually leads to termination.
• Avoid cycles in data by using techniques like cycle detection.

Performance Considerations for Recursive CTEs

While recursive CTEs are powerful, they can be costly in terms of performance. Here are some tips to optimize them:

1. Index your tables: Ensure that foreign key columns used in recursion have proper indexing.
2. Limit recursion depth: Some databases allow limiting recursion depth to prevent excessive computations.
3. Use UNION vs. UNION ALL: UNION performs deduplication, while UNION ALL is faster without uniqueness enforcement.
4. Consider alternative approaches: In some cases, nested sets or adjacency list models may perform better.

Conclusion

Recursive CTEs are an excellent tool for handling hierarchical data and self-referencing relationships. By understanding how they work and taking performance considerations into account, you can efficiently retrieve organizational structures, category trees, and other complex relationships in SQL.

 

Other interesting article:

How CTE (WITH) works in SQL? Best CTE (WITH) examples