3305 Main Street Ste 019 Vancouver, WA 98663

If my WHERE clause uses a mix of AND and OR comparisons, how are they evaluated?

Background

The WHERE clause of an SQL statement can contain multiple criteria that are joined together using AND or OR logic.  The comparisons on both sides of an AND must be true for the condition to be true.  With OR however, only one of the comparisons must be true for the condition to be true.

This article addresses what happens when a mix of AND and OR operations is used.  For example:

"JSTATUS"='In progress' OR "JSTATUS"='Unstarted' AND "JCITY"='Portland'

Answer

All ANDs are evaluated first and then the ORs. So the above example might not produce what the author of the condition intended.  In this example, the last two criteria would be evaluated first.  If the result of that comparison was true or the first criteria ("JSTATUS"='In progress') was true then the condition would be true.  This means that all jobs that are in-progress would be shown and only the unstarted jobs in Portland would be included.

This is the equivalent to:

"JSTATUS"='In progress' OR ("JSTATUS"='Unstarted' AND "JCITY"='Portland')

Using parenthesis can help you to group your conditions so that they are evaluated in the order you intend.  For example, the above condition could be re-written like this so that only Portland jobs that are unstarted or in-progress are returned:

("JSTATUS"='In progress' OR "JSTATUS"='Unstarted') AND "JCITY"='Portland'