which one is faster? and why is that? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

which one is faster? and why is that?

Author

Topic

Mckay
Starting Member

8 Posts

Posted - 2005-08-26 : 10:37:15

hi

just want to know which one is faster/better
between this 2 query:

select * from table1 a
join table2 b on a.field2 = b.field2
and a.field1 = 'ZZZ'

or

select * from table1 a
join table2 b on a.field2 = b.field2
where a.field1 = 'ZZZ'

and of course can u tell me why? and how it work?

thank alot

regards

Mckay

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 10:54:22

I think both are same except that the second query will work in Both SQL Server and Access but the first one in SQL Server only

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 11:20:36

Perhaps....but look at this


USE Northwind
GO

    SELECT * 
      FROM Orders o 
 LEFT JOIN [Order Details] d 
	ON o.OrderId = d.OrderID
WHERE CustomerID = 'VINET'

    SELECT * 
      FROM Orders o 
 LEFT JOIN [Order Details] d 
	ON o.OrderId = d.OrderID
       AND CustomerID = 'VINET'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-30 : 13:59:15

Adding additional criteria in the JOIN clause only returns different results when you are doing a LEFT join and the field you are adding is in the RIGHT table. If you do an INNER JOIN, then the results are identical between the JOIN and WHERE version. There's an article on this around here somewhere...

I don't have any explicit results to verify this, but I would suspect the WHERE version to be more efficient. Perhaps the Query Optimizer would produce the same plan, in which case the performance is the same, but the readability of the WHERE version is easier.

---------------------------
EmeraldCityDomains.com

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 00:11:05

From a readability and conde-maintainability perspective I would very VERY rarely put something in a JOIN that did not reference the table being joined. So the WHERE gets my vote.

This would be OK though:


select * 
from table2 b 
     join table1 a 
          on a.field2 = b.field2
         and a.field1 = 'ZZZ'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources