Re: Outer Join Syntax

I agree with this. I started the other way round, using Interbase, which has always supported SQL92 syntax.
Just consider (in a left join) that the table before the left join is the 'main' one...
Now this all changes if you introduce right outer joins!

I am personally really pleased that Oracle are now implementing this syntax, I think it is a lot cleaner to separate join predicates and the 'where' clause. And it allows you to simply code complex join conditions much more simply.

e.g. if you want to select data from a and b, which join on column c, but you want only data on the b side that fulfills some other condition (say on column d). If none fulfills that condition you still want your row from a. It gets messy with (+) - or you have to use inline views. Left join sorts it.
Maybe I am wrong and am missing something there (could someone advise?): select ... from a left outer join b on a.c = b.c and b.d = whatever -- nice with (+):
select ... from a,b where a.c = b.c (+) and (b.c is null or b.d = whatever) -- ugly
or
select ... from a, (select * from b where b.d=whatever) b where a.c = b.c(+) -- interesting

I haven't got hold of 9i yet, and am wondering whether mixing (+) and SQL99 in the same query is going to confuse Oracle/its optimiser (just asking because Interbase does get confused).

Raphael

"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 Thu Jun 20 2002 - 05:16:57 CDT