Re: Best way to use INNER JOIN?

From: BicycleRepairman <engel.kevin_at_gmail.com>
Date: Sun, 1 Jun 2008 20:20:07 -0700 (PDT)
Message-ID: <6f46e583-7871-4fed-ad60-1273dd402b1d@27g2000hsf.googlegroups.com>


On Jun 1, 12:58 pm, "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 is no "future proof" requirement to change -- the traditional syntax works, and will be supported for a long time. Personally, even though I've grown up with the standard oracle syntax, I prefer the ANSI syntax. Some of that is due to lazy programmer's syndrome -- I see a lot of views with where clauses like "where a.id=b.id and b.create_date > (sysdate -30) and a.userid = 23001 and a.id = c.caseid (+) and ..."
which makes it a mental exercise to separate join logic from business logic.
ANSI also makes you a little more honest about where the columns are coming from -- rather than "come up with a shortcut for each table, then force the reader to deal with it", you have to be specific. I haven't seen specific problems where the optimizer gets confused by one syntax vs. the other, it would surprise me if you're using the CBO (and if you're using RBO, then you have other issues before dealing with join syntax...).
So, on my projects I'm a 'poor man's Tom Kyte', and I get a lot of "how do I make this three page view fast?", or "why isn't this returning the right rows?" ... ANSI syntax is a lot cleaner in this case.
But I wouldn't go back and change existing (working) code. Lastly, NEVER NEVER NEVER use natural joins. "Hey Oracle, you figure it out" is not a reasonable directive! Received on Sun Jun 01 2008 - 22:20:07 CDT

Original text of this message