SQL Condition Order

[jocooper]

Are conditions in SQL statements processed in a specific order? Or just the order they are listed in?

If they are processed in the order they are listed, is it faster to do the filter conditions before the key comparisons?

For example, would...

"SELECT table1.fieldA from table1, table2 where tabletable2.ItemType = 2 AND table1.ItemNum = table2.ItemNum"

be faster than....

"SELECT table1.fieldA from table1, table2 where table1.ItemNum = table2.ItemNum AND tabletable2.ItemType = 2"

 

[Snugglebear]

Depends on the DBMS you're using. If it's worth its salt the integrated query optimizer will do all that for you, developing mulitple access plans and deciding which is the best. Some still don't do this, so I hand-optimize my own SQL based on left to right execution. That may be old-school, but frankly, you run into some wierd stuff in the real world.

 

[:LJ:]

I doubt that the order would make enough of a difference these days to be noticeable. What may make a difference is using INNER JOINs instead of lots of "=" conditions.

 

[jocooper]

I doubt that the order would make enough of a difference these days to be noticeable. What may make a difference is using INNER JOINs instead of lots of "=" conditions.

Which is faster

 

[:LJ:]

Which is faster

Subjectively, I'd say the JOINs are faster. They're also easier to maintain, which is the main reason I use them.