Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Elliott Sprehn and Chris Phillips and Nathan Strutz and Anant Pradhan and Dave DeVol and Byron Raines

SQL AND / OR Order of Operations

By Ben Nadel on
Tags: SQL

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:

  • 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:

  • 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:

  • 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:

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

Tweet This Titillating read by @BenNadel - SQL AND / OR Order of Operations Thanks my man — you rock the party that rocks the body!


Reader Comments

It's simple from the boolean logic point of view. AND is multiplication whereas OR is addition.

AND:
1 AND 1 = 1 <=> 1 * 1 = 1
0 AND 1 = 0 <=> 0 * 1 = 0
etc.

OR:
0 OR 0 = 0 <=> 0 + 0 = 0
0 OR 1 = 1 <=> 0 + 1 = 1
etc.

The only exception is
1 OR 1 = 1 <=> 1 + 1 = 1
but it makes sense because there is no value above 1.

The ordering of operations is the same as the order of simple arithmetic.

Reply to this Comment

@Unicode,

I'm sorry, but explaining that in terms of mathematical PEMDAS is.... brilliant!! Thanks!

Reply to this Comment

Thanks a lot Ben for this article. It cleared my confusion on whether AND operations are performed first or OR when there's no parenthesis. :)

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.