Use cases for Recursive CTEs

Introductions

Recursive CTEs are a way to reference a query over and over again, until the deemed end point or the termination check, as it is called. It’s worth noting that the example shown in this blog will be done on Azure SQL Database.

There is a certain structure with the use of recursive CTEs, which is as shown below:

Figure 1: Recursive CTE structure

There is an initial query which is known as the ‘anchor query’ which in the case of the image shown above, is selecting the number ‘1’ as ‘n’. There is a secondary query which is joined together with the use of a ‘UNION ALL’. The secondary query, also known as ‘Recursive Member’ (shown in the image above) is the query which runs continuously until the recursive member has hit the termination point (also shown on the image above).

Scenario

An individual is working in HR and wants to find out which individual is managing who. This may be for several reasons such as, they need to ask the managers on the progress of their staff and if their appraisal is coming up or is due.

Another scenario may be that the company is enrolling more staff and wants to find out the capacity of the current staff or find individuals who have not yet got anyone to manage to give them the opportunity to do so.

Solution

As mentioned above, the examples on this blog as completed on Azure SQL database. So, let’s get into the solution. This demo which I have prepared for this blog is widely used to showcase the use case of Recursive CTEs.

Figure 2: Dataset

This above is the dataset that we are going to be working with and the dataset that we will use in order to build our recursive CTE. So, in this example, we can see that there are 2 records which does not have a value populated for the column ‘ReportsTo’, which we will assume that they are the top managers.

To build our recursive CTE, we are going to start off with by selecting the records where the ‘ReportsTo’ equals NULL.

SELECT	employeeId,
        FirstName,
        LastName,
        ReportsTo
FROM 	Sales.Staff
WHERE 	reportsTo IS NULL

This shown above will be the anchor member to the Recursive CTE as shown in Figure 1. This will be the initial query that will set the foundation for the recursive member. Between the anchor query and recursive, the ‘union all’ join is used, like shown below:

UNION ALL

Next will be the recursive member which will run continuously until the termination check has been reached. Now let’s create the next 2 steps which includes the recursive member as well as the termination check which is seen in the inner join of the query.

SELECT	s.employeeId,
        s.FirstName,
        s.LastName,
        s.ReportsTo
FROM 	Sales.Staff s
     	INNER JOIN StaffHierarchy sh ON sh.employeeId = s.reportsTo

To check the final codebase, see below once the final touches has been completed. As you can see, the codes that were gone over above has been wrapped inside a ‘WITH StaffHierarchy AS’ which invokes the recursive CTE, or CTE for that matter.

WITH StaffHierarchy AS( 
SELECT EmployeeId,
          FirstName,
          LastName,
          ReportsTo
FROM 	  Sales.Staff
WHERE 	  ReportsTo IS NULL

UNION ALL
   
SELECT	  s.employeeId,
          s.FirstName,
          s.LastName,
          s.ReportsTo
FROM 	  Sales.Staff s

INNER JOIN StaffHierarchy sh ON sh.employeeId = s.reportsTo 
)

SELECT	employeeId,
        FirstName,
        LastName,
        ReportsTo
FROM 	StaffHierarchy ep

And finally, the code is finished off with a select from the CTE itself, to showcase the results of the recursive CTE.