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.