# 为什么我们不能在递归CTE中使用外部联接？ [英] Why cannot we use outer joins in Recursive CTE?

### 问题描述

` `;与GetParentOfChild AS （ SELECT  Rn = ROW_NUMBER（）超过（排序方式（选择1）），row_id AS父母，parent_account_id作为ParentId  FROM siebelextract..account  WHERE row_id = @ChildId  UNION ALL 选择 Rn + 1 ，a.row_id作为父母，a.parent_account_id作为ParentId  FROM siebelextract..account a  JOIN GetParentOfChild gp on a.row_id = gp.ParentId ） 选择TOP 1 @ChildId =父母 FROM GetParentOfChild 按Rn DESC命令排序 ` `

< p > 消息462，级别16，状态1，过程GetParent，第9行

• 选择区域

• 组别

• 拥有生活

• 标量聚合

• TOP

• 左，右，外连接（允许内连接）

• 子查询

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

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.