I previously explained the basics of common table expressions (CTEs) as well as how to chain them in sequence when you need multiple temporary result sets to get the results you desire. One of the topics I have not covered yet is using CTEs to write recursive queries.
The classic example of using a recursive CTE is displaying a hierarchy of employees. This is done using AdventureWorks database and the following query:
[code language=”sql”]
WITH CTE AS
(
SELECT
EmployeeID,
ManagerID,
LoginID
FROM HumanResources.Employee
WHERE EmployeeID = 3
UNION ALL
SELECT
Employees.EmployeeID,
Employees.ManagerID,
Employees.LoginID
FROM CTE AS Anchor
JOIN HumanResources.Employee AS Employees
ON Employees.ManagerID = Anchor.EmployeeID
)
SELECT * FROM CTE;
[/code]
Let’s dissect this query a bit. We can see that a common table expression is being used when we see the WITH keyword. The first query appearing before the UNION ALL keyword is known as the anchor query. This is the query that is used to begin recursion. The query that appears after the UNION ALL keyword is the recursive portion of the query. The recursive portion queries the CTE itself and continues to execute until no results are returned. In this example, the first iteration is the execution of the anchor statement, which will return the single employee with the ID of 3. After the anchor has been executed, the recursive portion begins executing. The first iteration of the recursive portion will return any employees that have a ManagerID of 3, which was the employee returned in the anchor portion. The next iteration will return the employees with a ManagerID equal to any of the EmployeeIDs returned in the first iteration of the recursive portion and so on until no more results are returned.
If this example is not clear enough, let’s take a look at another example. This time, we’ll use a recursive CTE to display a list of dates. Take a look at the code below:
[code language=”sql”]
WITH DateCTE AS
(
SELECT CAST(‘2011-01-01’ AS DateTime) AS TheDate
UNION ALL
SELECT DATEADD(DAY, 1, TheDate) FROM DateCTE WHERE DATEADD(DAY, 1, TheDate) <= CAST('2011-12-31' AS DateTime)
)
SELECT * FROM DateCTE ORDER BY TheDate OPTION(MAXRECURSION 0)
[/code]
This is another recursive CTE query. Again, the anchor statement appears before the UNION ALL keyword and the recursive statement appears after. The anchor statement in this case simply retrieves the date January 1, 2011. The recursive portion of the CTE then adds one day at a time to that date until December 31, 2011 is reached. Finally, we pull all of the data out of the CTE ordered by the date.
Note that we have included the OPTION(MAXRECURSION 0) keyword to allows use to recurse indefinitely to complete the execution of the CTE. Had we not included this, we would have seen an error stating that the maximum recursion limit was reached prior to query completion.
That's the basics of using recursive CTEs. Once you get comfortable with them, you'll see how powerful of a tool they are to have around. Are there any other examples of recursive CTEs that you can think of or any other examples you'd like to see? If so, leave a comment and let me know.