Recursion in SQL using CTE

• Download Recursive_Query_Using_CTE - 358 B

Introduction

I've often wondered how to use recursion in SQL. This article shows how to use Common Table Expressions (CTE or CTE's)to do this.

Background

SQL Server 2005 introduces a neat little thingy called CTE's. These can allow to you to do most of the things that derived tables can - but it also brings with it a more powerful capability - you can now write recursive queries in SQL using set-based logic. This capability is best explained with an example, so let's get down to it right away.

Printing an employee heirarchy

Look at the Employees table in the Northwind database. You will see that each employee has a ReportsTo column value set to an Employee ID. Let's say your task is to print out an employee heirarchy for Employee ID 5. What do you do? Resort to ADO.NET? With SQL 2005 you can use CTE's to do the same. Look at the code below:

WITH CTE_EMPLOYEE_HEIRARCHY AS
(
	SELECT E.EmployeeID, E.ReportsTo AS Supervisor, E.FirstName, E.LastName
	FROM EMPLOYEES E WHERE E.EmployeeID = 5
	
	UNION ALL

	SELECT E1.EmployeeID, E1.ReportsTo AS Supervisor, E1.FirstName, E1.LastName
	FROM CTE_EMPLOYEE_HEIRARCHY 
	JOIN EMPLOYEES E1
	ON E1.ReportsTo = CTE_EMPLOYEE_HEIRARCHY.EmployeeID 

)

SELECT * FROM CTE_EMPLOYEE_HEIRARCHY		

Let's look at each little block. The 'WITH CTE_EMPLOYEE_HEIRARCHY' simply names your CTE as CTE_EMPLOYEE_HEIRARCHY. The first SELECT statement is the 'anchor'.

SELECT E.EmployeeID, E.ReportsTo AS Supervisor, E.FirstName, E.LastName
FROM EMPLOYEES E WHERE E.EmployeeID = 5

It kicks off the recursive query by returning an employee row with ID = 5.

Next, the second SELECT gets the employees that report to EMP ID 5, but that's because only in the first run. Notice that this SELECT statement refers to the CTE itself:

SELECT E1.EmployeeID, E1.ReportsTo AS Supervisor, E1.FirstName, E1.LastName
	FROM CTE_EMPLOYEE_HEIRARCHY 
	JOIN EMPLOYEES E1
	ON E1.ReportsTo = CTE_EMPLOYEE_HEIRARCHY.EmployeeID 

So this statement will return all employees who report to Employee ID = 5. In this example, there's only one level of reportees (6,7 & 9). But if Employee ID's 6,7 or 9 would have had more reportees, in the next recursion step, the CTE name would be replaced by Employee ID's 6,7 & 9, & you would get the next level of reportees.

So where does the recursion stop? By default, recusion stops at a recursive level of 100 (configurable), or when an empty result set is returned, whichever is earlier - as in the case of the above example when the second recusive call to the CTE name would get resolved to 6,7 & 9 but the resultant empty set of reportees would be empty because 6,7 & 9 are the humble software devs who have nobody to delgate work to.