Re: Outer Join Syntax

The mistake found by the original poster in the 9.0.1 manual has been corrected in 9.2:

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/statements_103a.htm#2107297

Outer joins are one of those topics where you'd like people to already know all about them before they read the manuals, but that's always a tricky assumption. (Same thing with normalization and other design issues.)  

John

On Thu, 20 Jun 2002 18:18:28 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>In other words, you stick the plus on precisely the wrong side of the join.
>
>If it's a left outer join, stick the plus sign on the right side of the "="
>test.
>
>If it's a right outer join, stick it on the left side.
>
>Highly intuitive, I think you'll agree.
>
>Regards
>HJR
>
>
>"Raphael Ploix" <raphael.ploix_at_threex.co.uk> wrote in message
>news:3d10822b$0$8514$ed9e5944_at_reading.news.pipex.net...
>> left outer joins work like this:
>>
>> select ... from a left outer join b on a.c = b.c
>>
>> is equivalent to
>>
>> select ... from a, b where a.c = b.c(+)
>>
>> table b is the 'optional' data in both cases: (+) in Oracle SQL, left
>outer
>> join in ANSI.
>>
>> Hope this helps.
>>
>> Raphael
>>
>>
>>
>> "Daniel Z" <dziesnospammer_at_zianet.com> wrote in message
>> news:3d0e5b0a.0_at_oracle.zianet.com...
>> > Perhaps someone can clear this up for me.
>> >
>> > I was looking at Oracle's documentation:
>> >
>>
>http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/
>> > a90125/queries2.htm#2054625
>> >
>> > ****
>> > Oracle says that "To write a query that performs an outer join of tables
>A
>> > and B and returns all rows from A (a left outer join), use the ANSI LEFT
>> > OUTER JOIN syntax, or apply the outer join operator (+) to all columns
>of
>> B
>> > in the join condition. For all rows in A that have no matching rows in
>B,
>> > Oracle returns null for any select list expressions containing columns
>of
>> > B."
>> > ****
>> >
>> > My interpretation of this is that if our WHERE statement is A=B, the use
>> of
>> > the word LEFT is equivalent to putting the (+) on the RIGHT, and either
>> one
>> > would display all of the rows from A, and any matching rows from B (or
>> > nulls).
>> >
>> > Correct so far?
>> >
>> > BUT... then Oracle gives me their examples:
>> >
>>
>http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/
>> > a90125/statements_103.htm#2066678
>> >
>> > ****
>> > SELECT d.department_id, e.last_name
>> > FROM departments d LEFT OUTER JOIN employees e
>> > ON d.department_id = e.department_id
>> > ORDER BY d.department_id;
>> >
>> > Users familiar with the traditional Oracle outer joins syntax will
>> recognize
>> > the same query in this form:
>> > SELECT d.department_id, e.last_name
>> > FROM departments d, employees e
>> > WHERE d.department_id(+) = e.department_id
>> > ORDER BY d.department_id;
>> > ****
>> >
>> > WAIT A MINUTE! They're saying here that the use of LEFT is the same as
>> > putting the (+) on the left! I also looked this up in the 9i SQL Study
>> > Guide and they say the same thing. Groff's Complete SQL Reference says
>> > their opposites, like I understand it.
>> >
>> > Could somebody confirm which is right? I only have 8i to work with at
>the
>> > moment, so I can't test the ANSI stuff. ANY assistance would be
>> appreciated
>> >
>> > Daniel Z.
>> >
>> > *******
>> > Make sure there's "no spam" when replying
>> >
>> >
>>
>>
>

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/

The opinions expressed above are mine and do not
necessarily reflect those of Oracle Corporation.