Index performance on ON versus WHERE

The performance will be the same. The optimizer will recognize this and create the same plan.

On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.

For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.

You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.

SELECT * FROM salestable , custtable 
WHERE salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable 
JOIN  custtable 
ON salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable 
ON salestable.custaccount = custtable.accountnum 
WHERE salestable.dataareaid = custtable.dataareaid

Gives these execution plans

enter image description here


They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans.

I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE.

For OUTER JOINS moving the conditions can affect the semantics however.


In simple cases, it will be the same. However, I have seen very complex queries with several joins have significantly different plans. A recent one I was working on started with a table that has close to 6 million rows joined to about 20 different tables. Only the first join to this table was an inner join, all others were left outer joins. The filter in the where clause was parameterized something like this:

WHERE table1.begindate >= @startdate AND table1.enddate < @enddate 

This filter was used later in the plan instead of earlier. When I moved these conditions to the first inner join, the plan changed dramatically as the filter was applied early on in the plan to limit the result set and my CPU and elapsed time dropped by approximately 310%. So, as with many SQL Server questions, it depends.