How Recursive CTE Works?

Let's create a table and insert some columns in it. Write a simple Recursive CTE.

SQL
CREATE TABLE #MyEmployees  
(  
    EmployeeID smallint NOT NULL,  
    FirstName nvarchar(30) NOT NULL,  
    LastName nvarchar(40) NOT NULL,  
    Title nvarchar(50) NOT NULL,  
    DeptID smallint NOT NULL,  
    ManagerID int NULL,  
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)   
);

Populate the table with the following values.

SQL
INSERT INTO #MyEmployees VALUES
(1, 'HARISH', 'WARAN', N'Chief Executive Officer', 16, NULL),
(273, 'PARTHA', 'SARATHY', N'Vice President of Sales', 3, 1),
(274, 'PREAM', 'KEMAR', N'North American Sales Manager', 3, 273),
(275, 'VIJAY', 'KUMAR', N'Sales Representative', 3, 274),
(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
(16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
SQL
GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM #MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM #MyEmployees AS e
    INNER JOIN DirectReports AS d
    ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports

The result of “SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports” is.

EmployeeLevel

Now let's find out how this result is obtained.

SQL
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM #MyEmployees
WHERE ManagerID IS NULL

This is the base result set of the CTE structure; they are referred to as anchor members. And the result is.

Manager ID

This is the input for the next recursive operation (in other words ManagerID = d.EmployeeID). Here EmployeeID is “1” and the input to the next step is “1”.

SQL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM #MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID

This is equivalent to.

SQL
SELECT e.ManagerID, e.EmployeeID, e.Title
FROM #MyEmployees E
WHERE E.ManagerID = 1

Now the result set is.

Query output

The next input is 273, hence our query is.

SQL
SELECT e.ManagerID, e.EmployeeID, e.Title
FROM #MyEmployees E WHERE E.ManagerID = 273

The result is

Result

Thus for the next step, the input will be 16,274,285.

SQL
SELECT e.ManagerID, e.EmployeeID, e.Title
FROM #MyEmployees E
WHERE E.ManagerID IN (16,274,285)

The result is

Query result

Now ManagerId is 23,275,276,286.

SQL
SELECT e.ManagerID, e.EmployeeID, e.Title
FROM #MyEmployees E
WHERE E.ManagerID IN (23, 275, 276, 286)

Now the result set is empty.

When the result set is empty the recursion will stop and return the result in a union manner. Thus the final result is.

Result set is empty