SQL Server Join Condition vs WHERE Clause – Performance Impact

join;performancesql server

I've developed a habit of separating join conditions from other additional conditions. However I understand the logical execution order is:

  1. FROM
  2. WHERE

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.

Best Answer

Expanding on SMor's comment:

The short answer is no - it does not matter whether you put filters into the join or the where clause when you use INNER JOINs. Use outer joins changes the situation greatly. And as usual, there are no absolute answers to any performance question. If 2 queries are logically the same, you need to examine the execution plans to know which is more efficient.

For an OUTER JOIN, whether you put a condition on the WHERE clause or the JOIN itself can actually change the results.

SELECT TOP 500 p.id
FROM        dbo.Posts p
LEFT JOIN  dbo.Votes v ON p.id = v.PostId AND   v.VoteTypeId = 2
ORDER BY p.id

Will return records in Posts even if they don't have a matching Vote with VoteTypeId=2.

SELECT TOP 500 p.id
FROM        dbo.Posts p
LEFT JOIN  dbo.Votes v ON p.id = v.PostId 
WHERE  v.VoteTypeId = 2
ORDER BY p.id

the WHERE clause counteracts the outer join and drops records without a match in the Votes table.

Related Question