I've developed a habit of separating join conditions from other additional conditions. However I understand the logical execution order is:
Can it be detrimental to performance if I add additional conditions in the
where clause rather than the
join clause; or is this a part which typically gets simplified and handled equally in the phase of query optimization?
Here are two simple sample queries which both return the same plan:
USE StackOverflow2010; -- additional filters in where clause SELECT TOP 500 p.id FROM dbo.Posts p INNER JOIN dbo.Votes v ON p.id = v.PostId WHERE v.VoteTypeId = 2 ORDER BY p.id ; -- all criteria in on clause SELECT TOP 500 p.id FROM dbo.Posts p INNER JOIN dbo.Votes v ON p.id = v.PostId AND v.VoteTypeId = 2 ORDER BY p.id ;
I'd like to add that if I write longer analytical statements which typically span more than 100 lines (formatted) I try to reduce the result set as soon as possible typically using derived tables and adding an additional where there before it gets to the join.