Many people, once they start getting comfortable writing SQL, begin asking the same questions. One such common question is “Does the order I put stuff in the WHERE clause make a difference?” This question usually means different things to different people, so let’s dig in a bit.
There are often odd exceptions to almost any generic rule in SQL Server. However, the short answer, with Microsoft SQL Server, is “No,” or at least “Not usually.” SQL Server will internally re-order where clause items to properly match indexes or other time saving devices. The most notable exception to this is if items are grouped with parentheses.
That being said, other Database Management Systems (DBMS) may be impacted by where clause order. I also work with Oracle, Sybase SQL Anywhere and Pervasive DBMs and I note that from one system to another WHERE clause order can make a difference. If you’re not working with SQL Server, take all this with a grain of salt and do your own testing.
Earlier I mentioned that this question means different things to different people. The following examples will highlight what I mean:
Using tools to evaluate how SQL Server works the query (which I’ll get into in future articles), I confirmed that these two statements are equivalent:
WHERE HAVE_CUSSED_ME_BEFORE = 1
AND STOLE_MY_GIRLFRIEND = 0
WHERE STOLE_MY_GIRLFRIEND = 0
AND HAVE_CUSSED_ME_BEFORE = 1
Notice that the first statement begins by filtering on “Have Cussed Me Before” and in the second statement, these are reversed.
Others may actually be asking about the following example. Again, these two are equivalent:
WHERE GEEK_LEVEL = 25
WHERE 25 = GEEK_LEVEL
For additional performance related-topics, feel free to peruse my other posting in the Performance Tuning Series.