Re: Outer Join Syntax

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1024562626.12174.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> 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.
>

Uh huh. Intuition is a funny thing, no? <g>

> 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 ;(
>

My mind goes (and has done for years....). Sorry: that's a different issue!

My mind thinks "I want everything out of A even if it's not matched. So the table on the left is the one that I'm super-interested in." Hence my problem with the (+) on the right, and zero problem with "left outer join", apart from it causing the total destruction of my database.

>
> (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)

I must be one of those that left their predicates!

Different strokes and folks, I guess. Interesting to see how others look at things.

Regards
HJR
>
> --
> 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
> >>
> >>
> >>
>
>
>
Received on Thu Jun 20 2002 - 03:56:41 CDT