sql - How exactly does using OR in a MySQL statement differ with/without parentheses?

Question

I have this query:

SELECT * FROM (`users`) WHERE `date_next_payment` <= '2011-02-02' 
    AND `status` = 'active' OR `status` = 'past due'

Which does not return the correct results. However, adding parentheses around the OR conditions makes it work like so:

SELECT * FROM (`users`) WHERE `date_next_payment` <= '2011-02-02'
    AND (`status` = 'active' OR `status` = 'past due')

My question is why is it different? I understand that's is considering the OR statement differently without the parentheses; but I don't understand how it's different.

I haven't found any docs that have been helpful on this. If there's any links out there I'd really appreciate it.

Answer 1

This is because OR has lower operator precedence than AND. Whenever the DB sees an expression like

A AND B OR C

the AND is evaluated first, i.e. it is equivalent to

(A AND B) OR C

So if you explicitly want

A AND (B OR C)

instead, you must put in the parentheses.

This is btw not specific to SQL. The order of precedence of these operators is the same in all programming languages I know (i.e. at least C, C++, C#, Java and Unix shell scripts).