Index performance on ON versus WHERE

Question

I have two tables

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)

These tables have a non-clustered index on (Id, Date)

And I join these tables

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
WHERE 
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

This can also be written as

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

My question is, which of these two queries gives the better performance and why? Or are they equal?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

In general, where you put the filters makes a difference.
While Tom V says the Optimizer will recognize that the queries are the same and come up with the same plan, that is not always true. It depends on what version of SQL you are on, how complex your query is, and how important to the overall batch the Optimizer determines the query is.

The Optimizer may decide this portion of the batch isn't worth spending enough time on to allow it to come up with the best plan. In general you will get better performance if you put conditions that reduce the amount of data the query will need to work on in the ON clause instead of the WHERE clause (if possible, since doing this with an outer join will result in a Cartesian product. )

Its a little easier for the occasional SQL Developer to spot filters in the WHERE clause, but I've worked on some large tables where having the filters in the ON clause trims hours off the run time.

So if the clause has the potential to drastically reduce the number of rows the query will read, I will always put it in the ON clause to help the Optimizer pick the better plan.

Answer 5

Under ordinary circumstances, filter conditions can be specified either in WHERE or JOIN clauses. I tend to place filters under WHERE unless OUTER JOIN precedence could be affected (see below) or if the filter is very specific to that table (e.g. TYPE=12 to specify a specific subset of rows in the table).

On the other hand both ON and WHERE clauses can be used to specify join conditions (as opposed to filter conditions). As long as you are using only INNER joins, it still won't matter which you use under ordinary circumstances.

If you are using OUTER joins, however, it can make a great deal of difference. If, for example, you specify an OUTER JOIN between two tables (t1 and t2) but then, in the WHERE clause, go on to specify an eqijoin relationship between the tables (e.g. t1.col = t2.col), you have just converted the OUTER join to an INNER join! This is because WHERE can be used to specify an equijoin (or maybe even OUTER join, depending on the version, using the deprecated *= syntax) without using an ON clause, and when WHERE indicates an inner equijoin between tables, it overrides an OUTER JOIN (if present).

The original question was about filters, where the type of join often should not be an issue, but a join can also act as a filter and in those situations the placement of the join condition certainly can matter.

Answer 6

With INNER JOINs, it is a style issue.

However, it becomes much more interesting with OUTER JOINs. You should explore the differences between queries with OUTER JOINs and conditions in both the ON clause and the WHERE clause. The result-set is not always the same. Is, for example,

OUTER JOIN dbo.x ON a.ID = x.ID ... WHERE x.SomeField IS NOT NULL

the same as

INNER JOIN dbo.x ON a.ID = x.ID AND x.SomeField IS NOT NULL