SQL AND / OR Order of Operations
Posted July 10, 2006 at 2:59 PM
When is comes to SQL and a series of AND and OR directives in the WHERE clause, the order of operations can be confusing. I am not sure what the rules are to this, but this is what my personal experience has shown me. Even with no parenthesis, OR clauses create the same effect as adding parentheses. So for instance, the SQL query below:
Launch code in new window » Download code as text file »
- SELECT
- name
- FROM
- tag
- WHERE
- 1 = 0
- AND
- 1 = 0
- OR
- 1 = 1
- AND
- 1 = 0
- AND
- 2 = 2
- OR
- 1 = 1
- AND
- 2 = 2
... seems to be rewritten to be the same as:
Launch code in new window » Download code as text file »
- SELECT
- name
- FROM
- tag
- WHERE
- (
- 1 = 0
- AND
- 1 = 0
- )
- OR
- (
- 1 = 1
- AND
- 1 = 0
- AND
- 2 = 2
- )
- OR
- (
- 1 = 1
- AND
- 2 = 2
- )
Notice that the OR directive seem to just group the other directives (AND) in the where clause. That is why changing the last AND 2 = 2 to AND 2 = 3 will turn the whole WHERE clause to be untrue. So, even though the following looks like it would be true due to the OR 1 = 1 directives:
Launch code in new window » Download code as text file »
- SELECT
- name
- FROM
- tag
- WHERE
- 1 = 0
- AND
- 1 = 0
- OR
- 1 = 1
- AND
- 1 = 0
- AND
- 2 = 2
-
- -- Might think this OR directive will make everything true
- -- but it does not (see below).
- OR
- 1 = 1
-
- AND
- 2 = 3
... it turns out to be false when you take in the behind-the-scenes parentheses into account:
Launch code in new window » Download code as text file »
- SELECT
- name
- FROM
- tag
- WHERE
- (
- 1 = 0
- AND
- 1 = 0
- )
- OR
- (
- 1 = 1
- AND
- 1 = 0
- AND
- 2 = 2
- )
-
- -- This OR directive no longer returns true since you can see
- -- that it is not just OR 1 = 1, but in fact, it is an OR'ing
- -- of two separate AND directives that do not return true.
- OR
- (
- 1 = 1
- AND
- 2 = 3
- )
So, as far as order of operations go, it's not so much about AND and OR directives in the WHERE statement, but rather, how the behind-the-scenes parentheses will group the non-OR directives. In order for this to be clear, I would suggest always using the parenthesis when using AND and OR statements. Furthermore, even if it is not required, the parenthesis will make it easier for anyone else looking at your code to understand what is going on.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Duplicate() Much Faster Than ColdFusion Query-of-Queries
Older Post
Javascript Bible Update
Reader Comments
There are no comments posted for this web log entry.
