November 28, 2012 at 7:20 am
Hi All,
I am a newbie.Recently i came across a code as follow Select something from Handset h Inner Join Accessories a
on h.ModelName=a.ModelName
and h.ManufacturingYear=@Year In the above code consider the value of @Year as 2005.
Now my question is , Is there any difference between the first code and this codeselect something from Handset h Inner Join Accessories a
on h.ModelName=a.ModelName
where h.ManufacturingYear=@Year
I know that the output of both the code would be same. But does it has to do something with Performance or Standard
November 28, 2012 at 8:22 am
It would be interesting to see if the queries produced different execution plans. I'm gonna put that on my todo list !
November 28, 2012 at 8:22 am
Run both queries and look at the actual execution plan. They will be pretty much the same, if not identical. I don't like to see filtering in a join condition myself. If you have multiple joins and your filtering criteria is in the join it is hard to find. If this were my code I would use the second query where you have a where clause. They will in fact return the same thing but from a logic perspective it makes more sense to have a where clause instead of using the join as a where clause.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 8:29 am
patrickmcginnis59 (11/28/2012)
It would be interesting to see if the queries produced different execution plans. I'm gonna put that on my todo list !
Easy enough.
create table #Handset
(
Something varchar(10),
ModelName varchar(10),
ManufacturingYear int
)
create table #Accessories
(
ModelName varchar(10)
)
declare @Year int = 2005
Select something from #Handset h Inner Join #Accessories a
on h.ModelName=a.ModelName
and h.ManufacturingYear=@Year
select something from #Handset h Inner Join #Accessories a
on h.ModelName=a.ModelName
where h.ManufacturingYear=@Year
drop table #Accessories
drop table #Handset
Execution plan is identical in this case. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 8:38 am
I want to point out that the only time such criteria would make difference is when the join is an outer join rather than inner join because when criteria is in ON clause, it's evaluated before joining the table, allowing you to join in rows that would be otherwise excluded if the same criteria is in the WHERE clause with NULLs returned for the outer columns.
However, as Sean said, it's probably better to express it as a CTE or subquery to keep the code readable and to ensure the extra criteria isn't overlooked whether it's an inner or outer join.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply