Re: Best way to use INNER JOIN?
Date: Sun, 1 Jun 2008 14:34:11 -0700 (PDT)
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
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
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.
http://www.gplivna.eu Received on Sun Jun 01 2008 - 16:34:11 CDT