JOIN vs no JOIN

Is there any benefit in doing:

SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1> 0

rather than

SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id

TIA
Paul

Paul,

> Is there any benefit in doing:
>
> SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1>
0
>
> rather than
>
> SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id

IMO, the "join" syntax is the preffered syntax.

1 - it's "newer" (SQL92 as opposed to SQL89)
2 - it's easier to read (especially when joining multiple tables)
3 - it's more clear (you're "joining" to show that you're getting data from
multiple tables in the FROM clause of your statement, makes perfect sense)
4 - you can easily modify the join options (eg: LEFT JOIN) without having
to re-write the query
5 - think of "where" as restrictions on the output rather then a place of
joining tables



--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Martijn Tonies wrote:
> Paul,
>
>> Is there any benefit in doing:
>>
>> SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1>
> 0
>> rather than
>>
>> SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id
>
> IMO, the "join" syntax is the preffered syntax.
>
> 1 - it's "newer" (SQL92 as opposed to SQL89)
> 2 - it's easier to read (especially when joining multiple tables)
> 3 - it's more clear (you're "joining" to show that you're getting data from
> multiple tables in the FROM clause of your statement, makes perfect sense)
> 4 - you can easily modify the join options (eg: LEFT JOIN) without having
> to re-write the query
> 5 - think of "where" as restrictions on the output rather then a place of
> joining tables

6 - You do not suffer from any precedence changes between JOIN and ','

Greetings
Kai

--
This signature is left as an exercise for the reader.