1

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?

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • 1
    They should be the same. The SQL optimizer is only looking at filtering conditions, not where they are defined. Note: for outer joins the semantics can be different based on where the conditions are. – Gordon Linoff Aug 06 '16 at 13:58

1 Answers1

0

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

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • Can you check the execution plans for all the infinitely many possible queries we are talking about? – Lajos Arpad Aug 07 '16 at 08:35
  • @LajosArpad Yes I can ;) You can read about the SQL optimizer in the documentation. It is decomposing the sql statement and it is treating all filtering conditions the same way. – SQL Police Aug 07 '16 at 08:52
  • Is your statement valid for left joins as well? – Lajos Arpad Aug 07 '16 at 10:51
  • @LajosArpad Yes of course, but for a left join you must be careful in general. If you put a condition on the right-side table of a left join, then you could miss all records which are NULL as a result of the left join. – SQL Police Aug 07 '16 at 11:09
  • Please take a look here: http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause for the difference when we are talking about left joins – Lajos Arpad Aug 07 '16 at 13:36
  • @LajosArpad This is exactly what I meant. – SQL Police Aug 07 '16 at 14:55