CODEWITHSUNDEEP
sql-serverperformancejoinwhere-clauseon-clause
Lajos Arpad
Lajos Arpad

Reputation: 77083

Is an "on" criteria quicker than a "where"?

Let us suppose we have a join between t1 and t2, like this:

select c1, ..., cn
from t1 join t2
on t1.fk = t2.k
join t3
on t2.fk = t3.k
where (some condition independent from t3)

where (some condition independent from t3) might be anything. Now, consider this query:

select c1, ..., cn
from t1 join t2
on t1.fk = t2.k and (some condition independent from t3)
join t3
on t2.fk = t3.k

let us suppose that t1 x t2 x t3 along with the join conditions results in count1 rows, while if we filter by the where condition as well, then we will have count2 records, where count2 <= count1.

I wonder whether the where condition is executed for all the count1 rows, or just on the subset of count2 rows? Is it better in terms of performance to include conditions into the on conditions as soon as possible versus appending them at the end of the query?

Upvotes: 1

Views: 113

Answers (1)

SQL Police
SQL Police

Reputation: 4196

It makes no difference. Look at the actual execution plans. You will see that they are equal.

In SQL Server Management Studio: Query -> Include Actual Execution Plan

Upvotes: 0

Related Questions