Understanding recursive CTE

rec-cte.sql

-- taken from: http://www.databasejournal.com/features/mssql/article.php/10894_3502676_2/Common-Table-Expressions-CTE-on-SQL-2005.htm

-- recursive CTEs are divided in two parts 1. above "UNION ALL", and 2. below that
-- the statements on part 1 should be run without recursion to the CTE itself, this is our so-called recursion exit condition (anchor member)
-- the statements on part 2 use the CTE name in their select clause, this is the main recursion (recursive member)
-- the script below lists employees as well as their direct managers.

USE AdventureWorks ;
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS 
(
    SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.LoginID, e.ManagerID, e.EmployeeID
	FROM HumanResources.Employee e
    INNER JOIN DirectReports d
    ON e.ManagerID = d.EmployeeID 
)
SELECT * 
FROM DirectReports ;
GO