title: Why cannot we use outer joins in Recursive CTE?
body: <p>Consider the below</p>
  
  <pre><code>;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
  </code></pre>
  
  <p>What it does is that given any child , it will return the root level parent....The program is perfectly working fine all the time...</p>
  
  <p>Just out of curiosity/experimental sake i changed the JOIN to Left Outer Join and it reported </p>
  
  <p><strong>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'.</strong></p>
  
  <p>My question is why recursive part of CTE cannot accept Left Outer Join? Is it by design?</p>
  
  <p>Thanks</p>