Re: Best way to use INNER JOIN?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sun, 1 Jun 2008 17:24:57 -0700 (PDT)
Message-ID: <e20e7f60-7070-4c30-af9c-f9717dcd3530@l42g2000hsc.googlegroups.com>


On Jun 1, 5:34 pm, Gints Plivna <gints.pli..._at_gmail.com> wrote:
> On 1 Jûn., 19:58, "harry" <a..._at_abc.com> wrote:
>
>
>
>
>
> > Using Oracle 10g
>
> > I've always used this form of inner joins -
>
> >   1.  select BOOKSHELF.Title from BOOKSHELF, BOOK_ORDER where
> > BOOKSHELF.Title = BOOK_ORDER.Title;
>
> > A colleague tells me that I should be using this form (but can't tell me
> > why?) -
>
> >   2.  select Title from BOOKSHELF inner join BOOK_ORDER using (Title);
>
> > There is another way I've seen (but don't like) -
>
> >   3.  select Title from BOOKSHELF natural inner join BOOK_ORDER;
>
> > So, is there any difference between methods 1 and 2? if not is it safe to
> > continue using method 1 then?
>
> > thanks in advance
>
> > harry
>
> There are some differences between methods 1 and 2. Lets be more
> precise with method 2 one can do a bit more:
> 1) full outer joins possible
> 2) differentiate join condition vs where condition for outer joins see
> more here What's in a Condition? by Jonathan Gennick athttp://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition....
> 3) outer join a table to two tables see more herehttp://forums.oracle.com/forums/thread.jspa?messageID=2316186#2316186
>
> Question of course remains how often one needs that...
>
> So I learned the old style Oracle syntax and used that for ~10 years
> but now I slowly move to ANSI style, I think it is mostly just a
> preference of style. I personally don't damn neither of them, I can
> accept both and think that one can use synatx he understands and likes
> better.
> Speaking of natural join synatx - yea, I completely agree with almost
> everybody - this is completely dangerous synatx and a hidden time bomb
> as I've also mentioned in one of my blog posts here Natural joins are
> evil athttp://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-y...
>
> P.S. BTW just at the very end I found that for method 2 you are using
> "USING" clause. That's not the only possibility normally one uses
> select bs.Title from BOOKSHELF bs inner join BOOK_ORDER bo ON bs.title
> = bo.title. In general you won't always have the same joined column
> names, even more - using clause let you use only equi joins.
>
> Gints Plivnahttp://www.gplivna.eu- Hide quoted text -
>
> - Show quoted text -

Both the traditional inner join and the ANSI 1998 syntax are fully complaint with the ANSI standard. There have been numerous bugs associated with the newer join syntax and I find the traditional join syntax easier to understand so I still write my queries using it. If I need to do a full outer join I will use the new syntax. Otherwise the CBO seems to do better work with the traditional syntax at least on 9.2. I have not retested the queries we noticed CBO issues with in relation to the newer syntax under 10g.

IMHO -- Mark D Powell -- Received on Sun Jun 01 2008 - 19:24:57 CDT

Original text of this message