Is it correct, order of where clause doesn't matter when it is used with join?

Question

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?

Answer 1

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.

Answer 2

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

I couldn't immediately reproduce what you saw on any version of SQL Server from 2005 to 2014 inclusive. That's not really a huge surprise because I doubt I created the tables the same way you did, and we may not even have disabled the same optimizer rules. That's the value of being specific in a question and providing a complete reproduction script.

All that said, it is perfectly possible to create unusual-looking plans by disabling rules. The rules are designed to work together to produce the same (or very similar) execution plan result independent of syntax, where that is practicable. Selectively disabling rules has an impact on this of course.

It is also true that syntax and the written order of the query does frequently affect the final execution plan. Despite the foregoing, this is almost inevitable given the myriad ways SQL allows us to specify the same logical query specification.

Even so, it would be a mistake to infer anything you see from varying the written order of WHERE clause syntax elements in general. Write the query using syntax that seems logical and natural to you and your team members. Only try syntactical rewrites if you are unhappy with the execution plan the optimizer produces, and only after providing the optimizer with the best supporting indexes and statistics.