Re: Best way to use INNER JOIN?

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Sun, 1 Jun 2008 14:34:11 -0700 (PDT)
Message-ID: <0652ad14-5752-466c-8a96-e6db3dc5bc97@25g2000hsx.googlegroups.com>


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 at http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html 3) outer join a table to two tables see more here http://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 at http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html

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 Plivna
http://www.gplivna.eu Received on Sun Jun 01 2008 - 16:34:11 CDT

Original text of this message