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
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.