Order of evaluation of boolean expressions in SQL

Question

I'm having trouble figuring out the evaluation order of boolean predicates in SQL.

Consider the following selection predicate over our (imaginary) car database:

WHERE
make='Honda' AND model='Accord' OR make='VW' AND model='Golf';

I know that AND has precedence over OR, however I'm confused if this expression would be evaluated as follows:

((make='Honda' AND model='Accord') OR make='VW') AND model='Golf';

or as:

(make='Honda' AND model='Accord') OR (make='VW' AND model='Golf');

Or something completely different?!

Your help if very much appreciated.

Answer 1

This should be evaluated like

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

Explanation: In SQL server AND has precedence over OR and there fore you can imagine AND parts to be inside parenthesis and evaluated first and after them OR

Details based on your comments

AND has percedence over OR, it something I already mentioned in my post. This precedence is Left tor Right, therefore it is still not clear which evaluation order takes place here: ((make='Honda' AND model='Accord') OR make='VW') AND model='Golf'; or (make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf'); –

L2R parsing

1. WHERE (make='Honda' AND model='Accord') OR make='VW' AND model='Golf';

because first all ANDs and leftmost

2. WHEREresult1OR (make='VW' AND model='Golf');

because first all ANDs

3. WHEREresult1ORresult2;

finally OR

R2L parsing

1. WHERE make='Honda' AND model='Accord' OR (make='VW' AND model='Golf');

because first all ANDs and rightmost AND first

2. WHERE (make='Honda' AND model='Accord') ORresult1;

because first all ANDs over OR

3. WHEREresult2ORresult1;

finally OR

So in both cases the condition evaluates to

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

So I evaluated all three expressions in below query

   -- create table t(make varchar(100), model varchar(100))
   -- insert into t values ('Honda','Golf'),('Honda','Accord'),('VW','Golf'),('VW','Accord')
    select *, 
    case when make='Honda' AND model='Accord' OR make='VW' AND model='Golf' then 1 else 0 end as result,
    case when (make='Honda' AND model='Accord') OR (make='VW' AND model='Golf') then 1 else 0 end as result1,
    case when ((make='Honda' AND model='Accord') OR make='VW' ) AND model='Golf' then 1 else 0 end as result2
    from t
    ;

And the results show that result =result1 all the time, proving that it is evaluated as

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

See sqlfiddle demo