Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

execution-planjoin;sql server

I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.

I tried options like disabling rules and checked actual query execution plan.

select table1.col1, table2.col2
from T1 table1,
T2 table2
where table1.col1 = table2.col1
and table1.col3 = 10

Actual query execution plan in above case was showing join first and then filter when rule is disabled.

But when I changed the order of where clause with where tabel1.col3 = 10 and table1.col1 = table2.col1, it was filtering out the rows before join.

Thus I think in case of rules disabled order of where clause matters but not in case of rules enabled which is default condition.

So is it correct that order doesn't matter with default behavior of SQL Server?

Best Answer

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1, 
       b.col2 
 from table1 a 
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.

Related Question