Recursive CTE confusion in looping of the recursive member

user_16946838 0
Viewed 97

I'm having trouble understanding this recursive CTE in SQL, I can understand most parts but one of the areas that throw me off is the second subquery the recursive member. It joins the EmployeeCTE to the employees table, but by my logic, there will be only one row in the EmployeeCTE up to that point. It will contain only the row that contains managerID = NULL. Also, I get that after it loops through the recursive member it will add 1 to rank but how does it select the correct employee to increment by 1 and not any of the other rows?

USE Examples;

WITH EmployeesCTE AS
(
    -- Anchor member
    SELECT EmployeeID, 
        FirstName + ' ' + LastName As EmployeeName, 
        1 As Rank
    FROM Employees
    WHERE ManagerID IS NULL
UNION ALL
    -- Recursive member
    SELECT Employees.EmployeeID, 
        FirstName + ' ' + LastName, 
        Rank + 1
    FROM Employees
        JOIN EmployeesCTE
        ON Employees.ManagerID = EmployeesCTE.EmployeeID
)
SELECT *
FROM EmployeesCTE
ORDER BY Rank, EmployeeID;

SELECT * 
FROM Employees
correlated-subquery recursive-query common-table-expression sql
1 Answers

Every iteration of the recursive member will select the sub-employees of the previous one(s):

  • The anchor query retrieves all top level manager(s) who don't have any bosses above them.

  • Then, on iteration #1 the recursive query retrieves the immediate subordinates of the above, using the predicate Employees.ManagerID = EmployeesCTE.EmployeeID.

  • Then, on iteration #2 the recursive query only considers the extra rows retrieved in iteration #1 and applies the same predicate above to retrieve the subordinates of these.

  • Iteration #3 and so on, keep on considering only "newly added rows" every time, until no more rows are found in an iteration. When this happens the recursive CTE is complete and its execution ends.

user_64361910
Related