Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Outer Join Syntax
I wounder how you feel about the new ANSI/ISO JOIN syntax in Oracle 9i. I
have just run in to it as I study for the 9i SQL intro course. It looks like
some of these guys at ANSI took their college professors to heart when they
said make it intuitive. What happen to simplicity. An equal sign and plus
sign seem much simpler than six different phrases and still having to use
the equal sign. I am not sure what the ANSI SQL98 syntax was but I dont like
the new one. It seems that this mentallity of having a lot more memeory and
disk space why not use it is really catching on. I miss the days when a
programmer would spend days figuring out the most efficient, elegant way to
do something.
Bill Garcia
Tampa, FL
"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.
>
> 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
> >>
> >>
> >>
>
>
>
Received on Fri Jun 21 2002 - 10:42:39 CDT