Re: Best way to use INNER JOIN?

On 1 Jūn., 19:58, "harry" <a...@xxxxxxx> 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
.

Relevant Pages

  • Re: Foreign Key Idiom For Unknown Values
    ... is to add "ghost" rows to your reference table in ... outer joins that might need to be written. ... expensive to process than inner joins. ... foreign key value may not be known, consider adding a row in the other ...
    (microsoft.public.sqlserver.programming)
  • RE: Query not Returning Results
    ... Changing all INNER Joins to OUTER joins but when run a Synax Error was ... When i come to running the query i simply get no results returned? ...
    (microsoft.public.access.queries)
  • Foreign Key Idiom For Unknown Values
    ... outer joins that might need to be written. ... expensive to process than inner joins. ... foreign key value may not be known, consider adding a row in the other ... table that would be the unknown case. ...
    (microsoft.public.sqlserver.programming)
  • Re: Linked tables in a query giving no results
    ... change the joins between Projects and the other tables from inner joins ... to outer joins. ... Open the query in design view, click on the join line, ...
    (comp.databases.ms-access)