Re: ANSI Outer Joins

Paul Moore wrote in message <364af9de.21722496_at_news.origin-it.com>...
>On Thu, 12 Nov 1998 11:31:41 +0100, "Simon Caldwell"
><simonATgetrealsystemsDOTcom> wrote:
>
>>We have an application which runs on a number of other databases, and we
now
>>need to port to Oracle.
>>
>>The main problem we have, is that our SQL uses the ANSI standard SQL-92
>>outer join syntax, which allows multi-table outer joins.
>>I have looked on dejanews.com and can't find an answer to the problem,
just
>>discussion about levels of compliance with SQL-92.
>>We need to be able to achieve the same affect as
>>
>>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
>>
>>which would be possible using SQL-92 syntax, which is supported by the
other
>>databases, but appears not to be using Oracle.
>>
>I don't understand your question. The syntax you quote works on
>Oracle. If the syntax you are using is different, please post it...
>
>(Sorry, I don't know what SQL-92 looks like, so I can't say much abot
>that without examples...)
>
>Paul Moore
>

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

The SQL-92 syntax would be
select a.col1, b.col2, c.col3
from b

         JOIN c ON  (b.col4=c.col4)
         LEFT OUTER JOIN a ON (a.col1 = b.col1 AND a.col2 = c.col1)