Logical Processing of Recursive CTE

Question

I don't understand why the anchor is not called when the recursive member calls the cte.

Why does it go to the latest record (?) in the recursive part instead?

WITH Managers AS 
( 
--initialization 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers

Please include a dozen rows of sample data into the question, show us what result you expect to get and what result you are getting now. — Vladimir Baranov, Jan 27 '19 at 00:19
If what I got from this post is correct it answers my question: https://stackoverflow.com/questions/3187850/how-does-a-recursive-cte-run-line-by-line. The latest output of the recursive part is used for input in the recursive part. — John, Jan 27 '19 at 00:23
The anchor part is always executed. You say "Why does it go to the latest record?" -- Are you sorting the resulting rows? Do you have an extra filtering condition you are not mentioning? — The Impaler, Jan 27 '19 at 03:23
This is famous Northwind database and the query produces expected result with CEO (ReportsTo `NULL`) in the first line. — Alex Kudryashev, Jan 27 '19 at 03:34
Does [this](https://stackoverflow.com/a/54334978/92546) answer for a trivial rCTE help? — HABO, Jan 27 '19 at 03:56
Just a few days ago there was a [related question](https://stackoverflow.com/q/54333927/5089204) with some pretty good answers. In short: Think this more as an **iterative** and not really **recursive** action. Each new step *adds to the result* (similar to a series of `UNION ALL SELECT ...` — Shnugo, Jan 28 '19 at 08:01

Alex Kudryashev · Answer 1 · 2019-01-27T04:43:07.293

This is famous Microsoft sample database Northwind (immutable since 1997). The Emploeees table demonstrates hierarchical data and usage of recursive CTE (not available in 1997) to access it.

For better understanding / testing / training purpose add an extra column

WITH Managers AS 
( 
  --initialization 
  SELECT EmployeeID, LastName, ReportsTo, /*extra column*/ 0 [level]  
  FROM Employees 
  WHERE ReportsTo IS NULL 

  UNION ALL 

  --recursive execution 
  SELECT e.employeeID,e.LastName, e.ReportsTo, [level]+1 
  FROM Employees e 
  INNER JOIN Managers m ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers

This is result set.

EmployeeID  LastName             ReportsTo   level
----------- -------------------- ----------- -----------
2           Fuller               NULL        0 --anchor call
--now 'Managers' CTE is the anchor (level 0)
1           Davolio              2           1
3           Leverling            2           1
4           Peacock              2           1
5           Buchanan             2           1
8           Callahan             2           1
--now 'Managers' CTE is resultset level 1
6           Suyama               5           2
7           King                 5           2
9           Dodsworth            5           2

So the anchor executes always, recursive part executes iff the anchor returns row(s).

Logical Processing of Recursive CTE

1 Answers1