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).
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.
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:
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.