Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
9  
Don't use the deprecated ANSI-89 JOIN syntax and it'll be a non-issue... –  JNK Apr 13 '12 at 14:46
4  
Don't use implied joins, they are a sql antipattern. It is easy to get accidental cross joins and it is unlear that you meant a corss join when you did it deliberately which makes them harder to maintain. They are also harder tomanitain because in SQL Server, you can't use the implied outer join syntax using that syntax even as far back as SQL Server 2000, the syntax was not guaranteed to have correct results and you can't use it all in SQL Server 2008 unless you are in compaitibilty mode. Nor should you ever mix implied and explicit joins as the results are often incorrect. –  HLGEM Apr 13 '12 at 17:23

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.