Re: Outer Join Syntax

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
> >
> >
>
>
Received on Thu Jun 20 2002 - 03:18:28 CDT