Re: Outer Join Syntax

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
>
>
Received on Wed Jun 19 2002 - 08:06:16 CDT