sql – 同一个表上的2个外连接？

简单的问题,为什么限制在SQL中有2个外连接,即使使用不同的列也在同一个表上,检查下面的查询以便更好地理解.我也可以使用嵌套的子查询或ANSI连接来克服它们,但是为什么它甚至首先使用()运算符进行限制！

在这个问题中,我指的是错误：

ORA-01417: a table may be outer joined to at most one other table

我想问的是为什么允许这样做：

select * from
a,b,c
where a.a1 = b.b1
and a.a2 = c.c1

为什么不允许这样做：

select * from
a,c
where a.a1(+) = b.b1
and a.a2(+) = c.c1

请单独留下ANSI和嵌套子查询

解决方法

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions,which do not apply to the FROM clause OUTER JOIN syntax:

…

In a query that performs outer joins of more than two pairs of tables,a single table can be the null-generated table for only one other table. For this reason,you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.

这基本上意味着(用ANSI / ISO语法描述)你不能使用old()语法在ANSI / ISO中完全有效：

--- Query 1 ---
  a 
RIGHT JOIN b
  ON a.x = b.x
RIGHT JOIN c 
  ON a.y = c.y

要么：

--- Query 1b ---
  c 
LEFT JOIN 
    b LEFT JOIN a
        ON a.x = b.x 
  ON a.y = c.y

这只是旧Oracle语法的许多限制之一.

至于这种限制的原因,可能是实现细节或/和这种连接的模糊性.虽然上面的两个连接是100％等效的,但以下不等同于以上两个：

--- Query 2 ---
  a 
RIGHT JOIN c 
  ON a.y = c.y 
RIGHT JOIN b
  ON a.x = b.x

参见SQL-Fiddle的测试.所以问题出现了.应该如何解释专有连接,如查询1或2？

FROM a,c 
WHERE a.y (+) = c.y 
  AND a.x (+) = b.x

如果一个表出现在(2个或更多)外连接的左侧,则没有限制.即使使用旧语法,这些也是完全有效的：

FROM a
  LEFT JOIN b ON a.x = b.x 
 LEFT JOIN c ON a.y = c.y
  ...
  LEFT JOIN z ON a.q = z.q

FROM a,...,z
WHERE a.x = b.x (+) 
 AND a.y = c.y (+)
  ...
  AND a.q = z.q (+)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!