Why cannot we use outer joins in Recursive CTE?

问题:

Consider the below

;WITH GetParentOfChild AS
    (
        SELECT 
            Rn = ROW_NUMBER() Over(Order By (Select 1))
            ,row_id AS Parents
            ,parent_account_id  As ParentId 
        FROM siebelextract..account
        WHERE row_id = @ChildId
        UNION ALL
        SELECT 
            Rn + 1
            ,a.row_id as Parents
            ,a.parent_account_id As ParentId    
        FROM siebelextract..account a
        JOIN GetParentOfChild gp on a.row_id = gp.ParentId
    )

SELECT TOP 1 @ChildId = Parents 
FROM GetParentOfChild
ORDER BY Rn DESC

What it does is that given any child , it will return the root level parent....The program is perfectly working fine all the time...

Just out of curiosity/experimental sake i changed the JOIN to Left Outer Join and it reported

Msg 462, Level 16, State 1, Procedure GetParent, Line 9 Outer join is not allowed in the recursive part of a recursive common table expression 'GetParentOfChild'.

My question is why recursive part of CTE cannot accept Left Outer Join? Is it by design?

Thanks

回答1:

Yes, it is by design, read Guidelines for Defining and Using Recursive Common Table Expressions

The following items are not allowed in the CTE_query_definition of a recursive member:

• SELECT DISTINCT
• GROUP BY
• HAVING
• Scalar aggregation
• TOP
• LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
• Subqueries

Notice that if your query make a left join to it self through CTE can become to an infinite recursion.

回答2:

You can't use LEFT JOIN with recursive CTE but you can use OUTER APPLY which should give the same results.