oracle 同一个表上的 2 个外部联接？

2 Outer Joins on Same Table?

提问by Shubham Gupta

Here is a question which has been boggling me for few days now, and I searched and searched but couldn't find any convincing answer !

这是一个困扰我好几天的问题，我搜索并搜索但找不到任何令人信服的答案！

Simple question, why is it restricted to have 2 Outer Joins in SQL, on same table even with different columns being used, check the queries below for better understanding. Also I can overcome them using nested sub query or ANSI joins, but then why it is even restricted in the first place using (+) operator!

简单的问题，为什么在 SQL 中限制有 2 个外部联接，即使在使用不同列的同一个表上，请检查下面的查询以获得更好的理解。我也可以使用嵌套子查询或 ANSI 连接来克服它们，但是为什么它甚至首先使用 (+) 运算符受到限制！

In this question I'm referring to the error :

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

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

ORA-01417: 一个表最多可以外部连接到另一个表

What I want to ask is why this is allowed :

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

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

And why this is not allowed:

以及为什么不允许这样做：

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

Please leave ANSI and Nested SubQueries alone

请不要管 ANSI 和嵌套子查询

回答by ypercube??

The restriction is described in Oracle documentation: Outer Joins

Oracle 文档中描述了该限制：外部联接

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.

Oracle 建议您使用 FROM 子句 OUTER JOIN 语法而不是 Oracle 连接运算符。使用 Oracle 连接运算符 (+) 的外连接查询受以下规则和限制的约束，这些规则和限制不适用于 FROM 子句 OUTER JOIN 语法：

...

在执行两对以上表的外连接的查询中，单个表可以是仅另一个表的空生成表。因此，不能将 (+) 运算符应用于 A 和 B 的连接条件以及 B 和 C 的连接条件中的 B 列。有关外部连接的语法，请参阅 SELECT。

which basically means (described in ANSI/ISO syntax) that you can't have with the old (+)syntax what is perfectly valid in ANSI/ISO:

这基本上意味着（在 ANSI/ISO 语法中描述）您不能使用(+)在 ANSI/ISO 中完全有效的旧语法：

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

or:

或者：

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

That's only one of the many restrictions of the old Oracle syntax.

这只是旧 Oracle 语法的众多限制之一。

---

---

As for the reasons for this restriction, it may be implementation details or/and the ambiguity of such joins. While the two joins above are 100% equivalent, the following is not equivalent to the above two:

至于这个限制的原因，可能是实现细节或/和这种连接的歧义。虽然上面的两个连接是 100% 等价的，但以下不等价于上面两个：

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

See the test in SQL-Fiddle.So the question arises. How should the proprietary join be interpreted, as query 1 or 2?

请参阅SQL-Fiddle 中的测试。所以问题来了。专有连接应该如何解释为查询 1 或 2？

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

---

---

There is no restriction if a table appears on the left side of (2 or more) outer joins. These are perfectly valid, even with the old syntax:

如果表出现在（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, b, ..., z
WHERE a.x = b.x (+) 
? AND a.y = c.y (+)
  ...
  AND a.q = z.q (+)

回答by Lukasz Szozda

I strongly suggest to use explicit OUTER JOINsyntax. Starting from Oracle 12c this restriction is relaxed 1.4.3 Enhanced Oracle Native LEFT OUTER JOIN Syntax:

我强烈建议使用显式OUTER JOIN语法。从 Oracle 12c 开始，此限制放宽了1.4.3 Enhanced Oracle Native LEFT OUTER JOIN Syntax：

In previous releases of Oracle Database, in a query that performed outer joins of more than two pairs of tables, a single table could be the null-generated table for only one other table. Beginning with Oracle Database 12c, a single table can be the null-generated table for multiple tables.

在以前版本的 Oracle 数据库中，在执行两对以上表的外连接的查询中，单个表可能是另一个表的空生成表。从 Oracle Database 12c 开始，单个表可以是多个表的空生成表。

Code:

代码：

CREATE TABLE a AS
SELECT 1 AS a1, 2 AS a2 FROM dual;

CREATE TABLE b AS
SELECT 1 AS b1 FROM dual;

CREATE TABLE c AS
SELECT 3 AS c1 FROM dual;

-- Oracle 12c: code below will work
SELECT * 
FROM a, b, c
WHERE a.a1(+) = b.b1
  AND a.a2(+) = c.c1;

Output:

输出：

A1  A2  B1  C1
-   -   1   3

db<>fiddle demo - Oracle 11g will return error

db<>fiddle 演示 - Oracle 11g 将返回错误

db<>fiddle demo Oracle 12c/18c will return resultset

db<>fiddle demo Oracle 12c/18c 将返回结果集