CODEWITHSUNDEEP
sqlsql-serversql-server-2016
variable
variable

Reputation: 9654

How does recursive CTE without JOIN work?

I understand conventional CTE and recursion, like shown in the below example:

-- Create an Employee table.  
CREATE TABLE dbo.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 values.  
INSERT INTO dbo.MyEmployees VALUES   
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)  
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)  
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)  
,(275, N'Michael', N'Blythe', 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);

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  

    UNION ALL  

    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
    INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID   
)  
SELECT 
    ManagerID, EmployeeID, Title, EmployeeLevel   
FROM
    DirectReports  
ORDER BY 
    ManagerID;

The anchor is 1 row (returns R0), and then the recursion happens that gets the employees where that row is the manager (returns R1). And then gets employees where records in R1 is manager (returns R2) and so on.. Finally UNION ALL of all these results.

I have worked out a CTE where the anchor returns more than 1 row and there is no join condition involved in the recursive query. For example the below query returns a record for all dates between start and end date - in the below query there are 2 anchors (Bob and Phil), and the UNION ALL query does not perform any join. How does such CTE work? For example - 1st it will fetch the anchor rows. Then the recursive query will return date+1 for both of the anchor row? Then the next recursive query will return date+1+1 for all the prior rows? and so on, and then finally with do a UNION ALL. I could not find an online example of such a SQL, but it works well for me, in that - it returns row for each date between the start and end dates. I am not able to visualize how recursive CTE without a JOIN actually works, any help is appreciated.

DROP TABLE IF EXISTS tempdb.dbo.#dates

CREATE TABLE #dates
(
     NAME VARCHAR(50),
     START DATETIME,
     [END] DATETIME
)

INSERT INTO #dates
VALUES      
('Bob','2014-10-30','2014-11-02'),
('Phil','2014-10-10','2014-10-15')

;WITH cte AS 
(
    SELECT 
        NAME,
        [START],
        [END]
    FROM   
        #dates

    UNION ALL

    SELECT 
        NAME,
        DATEADD(DAY, 1, start),
        [end]
    FROM   
        cte
    WHERE  
        start < [END]
)
SELECT *
FROM cte 
ORDER BY 1,2

Upvotes: 0

Views: 803

Answers (1)

FrankPl
FrankPl

Reputation: 929

First of all, the anchor is not required to return only one row, it can return any size of result set. Note that SQL works on sets, not on records conceptionally.

Secondly, the requirement for the recursion is that it goes back to the CTE from the previous runs, but there is no requirement to join in external tables. And that is what you do. This recursion then continues until no more rows fulfill the second WHERE condition.

Hence, your interpretation of how it works is correct.

Upvotes: 1

Related Questions