November 11, 2014 at 10:01 pm
hi,
"Whereas the WHERE clause is semantically applied to the result of the join".
above line is from msdn.
1)
select t.*,t2.*,t1.*
from t
join t1 on tid=t1id
join t2 on tid=t2id and t2col=8 and t2co1 ='abbreviat1'
join t3 on tid=t3id and t3status=1
2)
select t.*,t2.*,t1.*
from t
join t1 on tid=t1id
join t2 on tid=t2id
join t3 on tid=t3id and t3status=1
where
t2col=8 and t2co1 ='abbreviat1'
3)
select t.*,t2.*,t1.*
from t
join t1 on tid=t1id
join t2 on tid=t2id
join t3 on tid=t3id
where
t2col=8 and t2co1 ='abbreviat1' and t3status=1
Q1)does that mean query 1) is faster than query 2)
Q2) which method i should follow 1) or 2) or 3)
yours sincerley
November 12, 2014 at 1:01 am
Q1: Not necessarily. Hard to say without actual execution plans. Because you are using INNER JOINS, semantically the three queries are the same; meaning they will produce the same result set.
Q2: it depends on what you want to achieve. Personally I usually write option 3, but if you are using LEFT OUTER JOINS, option 1 and 2 might give different results so I would choose depending on the requirements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 1:23 am
When it comes to a query that is only using inner joins, most chances are that you will get the exact same query plan for all the queries which means that all of them will have the same performance. In such case I would use option number 3, because I think that it is much more understandable when you see how you join the tables in the from clause and how to filter the rows in the where clause.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply