would using JOINS result in better performance than using WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

would using JOINS result in better performance than using WHERE clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-20 : 09:15:06

Gopi writes "I am wondering why lot of people use joins, when the same output can be achieved using conditions in the where clause.

for example...

SELECT t1.name
FROM table1 t1, table2 t2, table3 t3
WHERE t1.c1 = t2.c2 and t2.c3 = t3.c1

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.c1 = t2.c2
LEFT JOIN table3 t3 ON t2.c3 = t3.c1

Now would it be possible to say one is better than the other for any reason at all ??? if so what reasons please ??"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-20 : 09:22:28

The latter is 'a more approved syntax'....and will be supported longer (by m$) than the former...and in some instances can give more control and better performance over the former....

i'm sure there are other more elaborate/technical reasons which the others can contribute....but that's my 2c.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-20 : 09:34:14

Either way you write it 1)"Old Style" or 2)ANSI is a JOIN. For me, I find coding much more readable and flexible using ANSI style. I like to let the FROM handle the joining of multiple tables/views/derived tables/etc. and let the WHERE handle row selection.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-20 : 19:03:15

also by the way your two sql statements are NOT equivalent... the WHERE produces the effect of an INNER JOIN, not a LEFT (OUTER) JOIN.

- Jeff

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-21 : 03:29:43