Why cannot we use outer joins in Recursive CTE?

Question

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

Answer 1

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

So...

LEFT OUTER JOIN table tb2 on tb1.Prop = tb2.Prop

becomes...

OUTER APPLY (select * from table tb2 where tb1.Prop = tb2.Prop) tb2

Answer 2

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.

Answer 3

Another option would be to define a foundational CTE with de LEFT OUTER JOINS, and then use it as a table in your recursive CTE:

WITH 
InitialQuery as (
    -- here de left outer joins


),

GetParentOfChild AS
(
        FROM InitialQuery 
)

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