patternsqlMinor
Is it correct, order of where clause doesn't matter when it is used with join?
Viewed 0 times
orderwithusedwherejoindoesncorrectwhenclausematter
Problem
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.
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
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?
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 = 10Actual 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?
Solution
The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:
This keeps the join conditions separated from the filters.
select a.col1,
b.col2
from table1 a
join table2 b on b.col1 = a.col1
where a.col3 = 10This keeps the join conditions separated from the filters.
Code Snippets
select a.col1,
b.col2
from table1 a
join table2 b on b.col1 = a.col1
where a.col3 = 10Context
StackExchange Database Administrators Q#16477, answer score: 4
Revisions (0)
No revisions yet.