More About Common Table Expressions in SQL Server 2005 - A recursive common table expression: explanation
(Page 2 of 5 )
This section explains the script provided in the previous section. Let me explain it part by part. Let us first consider the following code fragment:
SELECT
EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID = 140
It is not surprising that it retrieves the list of all employees working under manager 140. Let us add a very funny structure to the above SELECT statement which ends up as follows:
WITH EmpCTE
AS
(
SELECT
EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID = 140
Even though we don’t come across any remarkable modifications, it has its own depth. Every row retrieved by the SELECT statement will be treated as part of the temporary table named “EmpCTE.” At this point, it is not of much interest. Let us proceed with the following statement now:
SELECT
b.EmployeeID, b.ManagerID
FROM HumanResources.Employee b
JOIN EmpCTE AS a ON a.EmployeeID = b.ManagerID
This is the most confusing statement you would ever see (or understand). The above statement works with every row present in “EmpCTE.” It primarily retrieves all the subordinates of each employee existing in “EmpCTE” and adds them again to the same “EmpCTE” with the help of the UNION ALL operator.
This process gets executed recursively for every new row added (or being added) to the same “EmpCTE.” Finally we retrieve all the information from “EmpCTE” using the following statement:
SELECT EmployeeID,ManagerID
FROM EmpCTE
Next: Displaying the depth of recursion when using recursive CTE: listing levels >>
More MS SQL Server Articles
More By Jagadish Chaterjee
