Fun with Operator Precedence

Let’s have some fun with operator precedence today. Specifically, logical operator precedence.

Specifically, how is it evaluated when your where clause says “WHERE This AND That OR Something AND that”, without any clarifying parenthesis?

Let’s play around with this. The simplest test scenario is a SELECT 1. If I get a 1 back, that means my WHERE clause evaluated to true, right? Right.

So this will definitely return a 1:   SELECT 1 WHERE 1=1;
And this won’t:   SELECT 1 WHERE 1=2;

This will also return a 1:   SELECT 1 WHERE 1=1 and 2=2;
And this won’t:   SELECT 1 WHERE 1=2 or 2=1;

Query 1

But here’s the fun part. What if I open up a query that looks like this:

SELECT 1
WHERE 1 = 1
   AND 2 = 1
   AND 1 = 1
   OR 1 = 1
   AND 2 = 1;

How does THAT evaluate? Well we need to glance at the operator precedence chart to see what SQL Server evaluates first. We’re concerned here with AND vs. OR…and it turns out that AND is evaluated first.  So, we can visualize – and indeed, clarify – the query above with the judicious use of parentheses:

SELECT 1
WHERE 
   (1 = 1
   AND 2 = 1
   AND 1 = 1)
   OR 
   (1 = 1
   AND 2 = 1);

(Note, by the way, that parentheses are actually operators in their own right. MSDN says: “Use parentheses to override the defined precedence of the operators in an expression. Everything within the parentheses is evaluated first to yield a single value before that value can be used by any operator outside the parentheses.” Here though, we’re using them not to override precedence, but to highligh which parts SQL is evaluating first.)

So that first parenthetical set: (1=1 and 2=1 and 1=1) obviously evaluates to FALSE. The second set ALSO evaluates to false (1=1 AND 2=1). We could translate it to this pseudocode:

SELECT 1
WHERE 
   (this evaluates false)
   OR 
   (this evaluates false);

So, this query would not get us our SELECT 1.

Query 2

But this query would:

SELECT 1
WHERE 1 = 1
   AND 2 = 1
   AND 1 = 1
   OR 1 = 1
   OR 1 = 1
   AND 2 = 1
   OR 1 = 1;

Why? Let’s sort it out visually again, using parentheses. Remember, all of the ANDs get evaluated first, so let’s group those with parentheses:

SELECT 1
WHERE 
   (1 = 1
   AND 2 = 1
   AND 1 = 1)
   OR 1 = 1
   OR 
   (1 = 1
   AND 2 = 1);

Ah, there it is. All of those ANDs get sorted out first, and then the ORs are considered. Or to write it in pseudocode:

SELECT 1
WHERE 
   (this evaluates false)
   OR this evaluates true
   OR 
   (this evaluates false);

Your friend, the parenthesis

All of this playing with parentheses so far has just been for visual aid.  But as we noted before, parenthesis are actually ironclad rulemakers in their own right. You can use parentheses to force the evaluation order that you want, so that this does NOT return a 1:

SELECT  1
WHERE   1 = 1
        AND 2 = 1
        AND ( 1 = 1
              OR 1 = 1
              OR ( 1 = 1
                   AND 2 = 1
                 )
              OR 1 = 1
            );

…even though, without the parentheses, it’s just Query 2 all over again. The parenthesis forces SQL to evaluate the query thusly:

SELECT  1
WHERE   evaluates to true
        AND evaluates to false
        AND ( evaluates to true
            );

Go on, play with a few simple examples yourself. See how it all fits together.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen