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

if you are unsure about the evaluation order (me too, btw), you always can set parentheses as needed. So you "define" your evaluation order yourself, and even if the sql interpreter changes its evaluation behaviour, the result will still be the same.

I know that this does not really answer your question, but why bother with evaluation order, if you can define it yourself with the use of ()?

Answer 2

I believe the order will be

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

1) make = 'Honda' and model = 'Accord'
2) Or make = 'VW' AND model = 'Golf'   

Thats means:

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

Thats because in most DBMS AND is the upper hand over OR

EDIT: Yes, condition have also precedence from left to right, but this is the same as order by:

SELECT * FROM YourTable
ORDER BY col1 DESC,
         col2 ASC,
         col3 DESC

Here col1 get presendence first, then col2, and then col 3. Same goes for our situtation, first presendence is AND over OR , second one is LEFT to RIGHT

Answer 3

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

Answer 4

I second thoughts of people who answered above. Since AND has precedence over OR, all the AND expressions should be evaluated first then their results will be evaluated with OR i.e. it should be

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