2 External connections on the same table?

Question

Here is a question that depressed me for several days, and I searched and searched, but could not find a convincing answer!

A simple question: why is it limited to have 2 External Connections in SQL, in the same table, even with different columns used, check the queries below for a better understanding. I can also overcome them with the help of nested subqueries or ANSI connections, but then why is it even limited in the first place with the (+) operator!

In this question, I mean the error: "ORA-01417: a table can be external, connected to no more than one other table"

I want to ask:

Why is this 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 only ANSI and nested SubQueries

Answer 1

The limitation is described in the Oracle documentation: External Connections

Oracle recommends using the FROM syntax instead of the OUTER JOIN instead of the Oracle join operator. External join requests that use the Oracle (+) join operator obey the following rules and restrictions that do not apply to FROM FROM OUTER JOIN syntax:

...

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

which basically means (described in ANSI / ISO syntax) that you cannot have with the old (+) syntax, which works great for ANSI / ISO:

 --- Query 1 --- a RIGHT JOIN b ON ax = bx RIGHT JOIN c ON ay = cy 

or

 --- Query 1b --- c LEFT JOIN b LEFT JOIN a ON ax = bx ON ay = cy 

This is just one of the many limitations of the old Oracle syntax.

---

As for the reasons for this limitation, these may be implementation details and / or the ambiguity of such associations. Although the two compounds above are 100% equivalent, the following is not equivalent to the two above:

 --- Query 2 --- a RIGHT JOIN c ON ay = cy RIGHT JOIN b ON ax = bx 

See the test in SQL-Fiddle . So the question is. How to interpret a proprietary connection as request 1 or 2?

 FROM a, b, c WHERE ay (+) = cy AND ax (+) = bx 

---

There is no limit if the table appears on the left side (2 or more) of the outer joins. They are perfectly true, even with the old syntax:

 FROM a LEFT JOIN b ON ax = bx LEFT JOIN c ON ay = cy ... LEFT JOIN z ON aq = zq FROM a, b, ..., z WHERE ax = bx (+) AND ay = cy (+) ... AND aq = zq (+)