Order of operation for AND and OR in SQL Server queries

Question

I have a sql statement (inherited) that has the following WHERE clause:

WHERE
          (Users_1.SecurityLevel IN ('Accounts', 'General manager'))
      AND (PurchaseOrders.Approval = 1)
      AND (PurchaseOrders.QuotedAmount = 0)
      AND (Users_1.StaffNumber = ISNULL(ServiceRequests.POC_UserID, PurchaseOrders.Approval_UserID))
      OR
      (Users_1.SecurityLevel IN ('Accounts', 'General manager'))
      AND (PurchaseOrders.QuotedAmount = 0)
      AND (ServiceRequests.POC = 1)
      AND (Users_1.StaffNumber = ISNULL(ServiceRequests.POC_UserID, PurchaseOrders.Approval_UserID))
      OR  
        (ISNULL(ISNULL(PurchaseOrders.InvoiceNumber, ServiceRequests.InvoiceNumber), '!#') <> '!#')
      AND (Users_1.StaffNumber = ISNULL(ServiceRequests.POC_UserID, PurchaseOrders.Approval_UserID))

I'm trying to figure out the order of operations when things are not nicely bracketed.

How are AND and OR statements ordered in the above example?

Is there an easy rule so that I can put brackets around things to make it more readable?

I'm looking for something like "BODMAS" when it comes to the mathematical order of operations, for SQL WHERE clause operators.

Thanks

Figueroa

Answer 1

The page on Operator Precedence tells you:

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

And that AND has a higher precedence than OR.

However, it's not correct. In SQL, you tell the system what you want, not how to do it, and the optimizer is free to re-order operations, provided that the same logical result is produced.

So, whilst operator precedence tells you how the operators are logically combined, it does not, in fact, control the order in which each piece of logic is actually performed. This means that idioms which may be safe in other languages because of guarantees of execution order are not in fact safe in SQL. E.g. a check such as:

<String can be parsed as an int> && <convert the string to an int and compare to 20>

Can be perfectly safe in languages such as C#. The same logic in SQL is not safe since the optimizer may choose to perform the string to int conversion before it evaluates whether the string can be parsed as an int and so can throw an error about a failed conversion. (Of course, it can also work as you may have expected and not produce an error)

Pantomimist

Thanks for the good, clear answer. So it seems to me from your explanation that I could put brackets around the "AND"s to make it more readable and get the same result: "(this AND this AND this) OR (that AND that AND that)" – Figueroa

I think it's a good idea to provide source when you contradict a piece of official documentation. – Pilsudski

Answer 2

And that AND has a higher precedence than OR.

That's totally correct:

-- ("from dual" is a dummy table for Oracle)

select 'true' from dual
where 1=0 and 2=1 or 3=3 -- returns 'true'

-- is identical to

select 'true' from dual
where (1=0 and 2=1) or 3=3 -- returns 'true'

-- and is identical to

select 'true' from dual
where 3=3 or (1=0 and 2=1) -- returns 'true'

-- order doesn't matter

but

select 'true' from dual
where 1=0 and (2=1 or 3=3) -- no rows!!!

Rule of thumb: enclose your OR(s) with parentesis, always. Even if it's not needed it makes code more clear to other (or yourself in a future, maybe).

Vannavannatta