Re: Best way to use INNER JOIN?

From: Gints Plivna <>
Date: Sun, 1 Jun 2008 14:34:11 -0700 (PDT)
Message-ID: <>

On 1 JŻn., 19:58, "harry" <> wrote:
> Using Oracle 10g
> I've always used this form of inner joins -
> † 1. †select BOOKSHELF.Title from BOOKSHELF, BOOK_ORDER where
> 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 3) outer join a table to two tables see more here

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

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 Received on Sun Jun 01 2008 - 16:34:11 CDT

Original text of this message