Re: ANSI Outer Joins

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 13 Nov 1998 22:49:13 GMT
Message-ID: <364cb6a1.46030981@news.siol.net>

On 13 Nov 1998 13:07:48 GMT, GreMa_at_t-online.de (Matthias Gresz) wrote:

>Hi,
>
>Simon Caldwell schrieb:
>>
>> >> ....[SNIP]....
>> >>select a.col1, b.col2, c.col3
>> >>from a, b, c
>> >>where a.col1(+) = b.col1
>> >> and a.col2(+) = c.col1
>> >> and b.col4=c.col4
>>>> > ....[SNIP]....
>> No it doesn't. Note that A is outer joined to both B and C. This will give
>> an error
>> ORA-01416: two tables cannot be outer-joined to each other
>
>Maybe you should specify the version you use. With our NT 7.3.4.2.0 this
>works a few dozens times a day:

Matthias, you are missing the point. Eech NULL-generating table in an Oracle's implementation of the other join can be outer joined only to one table as Simon correctly pointed out. So the original example is breaking this rule, because table A is NULL-generating table for both B and C. The example you provided is quite different and is not breaking the above rule. If I implicate your example to the original one, it is something like:

where a.col1 = b.col1(+)
  and a.col2 = c.col1(+)
  and b.col4=c.col4

As you can see, here both tables with (+) operator (tables B and C) are NULL-generating tables to only one joined table (in both cases it is table A), so the above rule is not being broken and Oracle will allow it. But the logic of this example is fundamentaly different compared to the original (syntacticaly incorect) example.

>Regards
>
>Matthias

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Nov 13 1998 - 16:49:13 CST