Re: Outer Join Syntax

Jonathan Lewis wrote:
>
> My mind must work the wrong way round:
> a.c = b.c(+)
> tells me "intuitively" that I am ADDing a spurious
> null row to table B that will match any row in A.
> B is the deficient table that is 'causing' my problem,
> so I add something to it.
>
> Whereas with
> select ... from a left outer join b on a.c = b.c
> I find it very hard to remember that I am using
> a LEFT join because the table on the RIGHT
> has lost some data ;(
>
> (I would be cautious about making comments
> about 'sticking the plus sign on the right side
> of the equals' - some people right their predicate
> back to front, viz: b.c(+) = a.c)
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK July / Sept
> Australia July / August
> Malaysia September
> USA (MI) November
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Howard J. Rogers wrote in message ...
> >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
> >>
> >>
> >>

I'm with you on this one...I've always read

a.x = b.x(+)

as

"I'll get rows from b plus (+) some nulls in the result"

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."