Recursive CTE in SQL Server

CTE which is the abbreviation for Common Table Expression is an SQL Server tool that returns a temporary data set that can be used by another query. In this article, we are going to learn about the Implementation of Recursive CTE in SQL servers. We will understand how recursive common table expressions work step by step and understand their workflow through various examples. Make sure you are familiar with Common table Expressions in SQL before diving into the Recursive CTE’s.

The Common Table Expressions (CTE) were introduced in SQL Server back in 2005. It is a temporary named result set that can make complex queries simple to write and understand by breaking them into smaller parts. The CTEs can be referenced with INSERT, UPDATE, DELETE, or SELECT statements in SQL. CTE returns a temporary result set that is referenced by another query. As the result set is temporary, it is not stored anywhere in the memory and thus it makes CTE an effective tool that can be used for reference to any other table.

A recursive CTE references itself during the execution. It returns a subset of the result, then it recursively references itself and terminates when it has returned all the results.

Recursive CTEs enable the user to process hierarchical data and allow to join all levels of hierarchy present in the database.

Syntax:

WITH RECURSIVE 
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;

Explanation of Syntax:

• cte_name: Name given to recursive subquery written in subquery block.

• col1, col2, …colN: The name given to columns generated by subquery.

• Subquery: A MySql query that refer to itself using cte_name as its own name.

The workflow of RECURSIVE CTE is majorly consists of these 5 steps:

• Anchor Member Execution: The CTE starts it’s execution with the anchor member which is a non recursive query. It initializes the initial rows of the CTE that serves as the starting point of the recursion.

• Recursive Member Execution(Iterations): The recursive member mainly consists of the SELECT statement that references the CTE itself. Each iteration uses the results that were obtained in the previous iteration or the initial anchor member.This process is repeated until a termination condition is met.

• Termination Condition Check: The termination condition is essential for the termination of the recursive query in the CTE or else our query can get stuck in an infinite loop. This mainly consists of WHERE clause that filters the rows. When the termination condition is met the recursive member halts the execution and returns the result set.

• Union Result Sets: The UNION ALL operator combines the results from the anchor member as well as the results obtained from all of the iterations of the recursive member. At this step the final result set is prepared which will be returned to the user.

• Return Final Result: Finally the result set formed in the previous steps is returned to the user as the output generated by the recursive CTE.

Let us consider the following table Organization on which we want to find the Hierarchical relationships between the employees and managers of a respective organization.

Example 1: Find Hierarchical Relationship of All Levels

Query:

WITH RecursiveOrganizationCTE AS
(
    SELECT EmployeeID, FirstName, LastName, Department,ManagerID
    FROM Organization
    WHERE ManagerID IS NULL
    UNION ALL
   
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.department,e.ManagerID
    FROM Organization e
    JOIN RecursiveOrganizationCTE r ON e.ManagerID = r.EmployeeID
)
--Show the records stored inside  the CTE we created above
SELECT *
FROM RecursiveOrganizationCTE;

Output:

Explanation: The following query uses a recursive CTE to extract hierarchical structure of a table based on the organizational relationships within the table Organization. The query shows the managers who are also employees within the organization at the top level as they have managerID as NULL , then it shows employees who reports to managers as well as act as managers themselves. At last the employees who are not managers who belongs to the last level of the hierarchical structure is shown in the result set.

Example 2: Simple Recursive CTE to Find Out Days in a Week

In the following example we will see how we can use the Recursive CTE in SQL server to extract all the days in a week easily.

Query:

WITH DaysofWeek(x, WeekDays) 
AS (
    SELECT 0, DATENAME(DW, 0)
    
    UNION ALL
    SELECT   x + 1, DATENAME(DW, x + 1)
    FROM  DaysofWeek  WHERE x < 6
)
SELECT WeekDays FROM DaysofWeek;

Output:

Explanation: The following query extracts all the days in a week with the help of Recursive CTE and DATENAME() function. The DATENAME() function returns the name of the weekday based on the weekday number. The anchor member is set as 0 and returns the Monday. The recursive member returns the next day of the week starting from tuesday till Sunday based on the value of x which is incremented by 1 recursively after each call.

Example 3: Simple Recursive CTE to Find Out All the Months in a Year

In the following example we will see how we can use the Recursive CTE in SQL server to extract all the months in a year .

Query:

WITH RecursiveMonths AS (
    SELECT
        1 AS MonthNumber,
        DATENAME(MONTH, CAST('2024-01-01' AS DATE)) AS MonthName

    UNION ALL

    SELECT
        MonthNumber + 1,
        DATENAME(MONTH, DATEADD(MONTH, MonthNumber, '2024-01-01'))
    FROM RecursiveMonths
    WHERE MonthNumber < 12
)
SELECT * FROM RecursiveMonths;

Output:

Explanation: The following query is used to extract all the months in a year with the help of Recursive CTE and DATENAME() function. The DATENAME() function returns the name of the month based on the month number. The anchor member is set as 1 and returns the January month. The recursive member returns the next month of the year starting from February to Decemeber based on the value of monthnumber which is incremented by 1 recursively after each call.

Example 4: Find Relationship Between Data Present in the Different Tables Through Recursive CTE.

In the following example we will see how we can use the Recursive CTE to extract meaningful data from multiple tables. Let us consider two tables CityData and CityRoutes on which we will perform our operations.

After inserting some data into the CityData Table, The Table Looks:

After inserting some data into the CityRoutes Table, The Table Looks:

Query:

WITH Destinations AS (
    SELECT
        RouteID,
        SourceCityID,
        DestinationCityID,
        CAST(CityName AS VARCHAR(MAX)) AS Route,
        Distance
    FROM CityRoutes CR
    INNER JOIN CityData CD ON CR.SourceCityID = CD.CityID

    UNION ALL

    SELECT
        CR.RouteID,
        CR.SourceCityID,
        CR.DestinationCityID,
        CAST(R.Route + ' -> ' + CD.CityName AS VARCHAR(MAX)),
        R.Distance + CR.Distance
    FROM Destinations R
    INNER JOIN CityRoutes CR ON R.DestinationCityID = CR.SourceCityID
    INNER JOIN CityData CD ON CR.DestinationCityID = CD.CityID
)
SELECT Route, Distance
FROM Destinations
WHERE SourceCityID = 3; 

Output:

Explanation: The following query uses Recursive CTE as well as multiple JOINS to find out the best route and it’s total distance from the id of the source city provided by the user in the query. The anchor member SELECT selects the routes from the CityRoutes table joining it with the CityData table. The recursive member UNION ALL selects additional routes by joining the CityRoutes table with the CTE itself.

In the following article, we have learned how Recursive common table expressions work in SQL server. We have learned the workflow of Recursive CTE along with different examples. We have learned how we can use recursive CTEs to find out the hierarchical relationships within a table and we have also learned how we can use recursive CTE along with two different tables as well. We hope this article has helped you to understand Recursive CTE in SQL Servers.