简单的问题,为什么限制在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 (+)
