23

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.

drewjoh
  • 1,786
  • 1
  • 14
  • 22

8 Answers8

37

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).

Péter Török
  • 114,404
  • 31
  • 268
  • 329
5
SELECT * FROM (`users`) WHERE `date_next_payment` <= '2011-02-02' 
AND `status` = 'active' OR `status` = 'past due'

In this example, you will get all records where either

a) The date_next_payment is before 2nd Feb 2011 AND status is active

b) The status is past_due

So the past_due records will not be restricted by date.

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

In this example, you will get all records where

a) The date_next_payment is before 2nd Feb 2011

AND

b) The status is either active or past_due

The brackets work like they do in maths or logic - the statements inside the brackets get evaluated first... so imagine seeing each step take place like this:

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

So a record is encountered with a status of active...

SELECT * FROM (`users`) WHERE `date_next_payment` <= '2011-02-02'
AND (TRUE OR FALSE)

This then evaluates, with the OR condition... (TRUE OR FALSE) == TRUE

SELECT * FROM (`users`) WHERE `date_next_payment` <= '2011-02-02'
AND TRUE

And the date is 2011-01-01

SELECT * FROM (`users`) WHERE TRUE
AND TRUE

And finally, TRUE AND TRUE == TRUE

SELECT * FROM (`users`) WHERE TRUE

And so the record is returned...

Imagining your query being executed in steps like this against each row in the database sometimes helps to understand where to put your brackets.

Fenton
  • 241,084
  • 71
  • 387
  • 401
2

in 

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

it will consider first date_next_payment <= '2011-02-02' AND status = 'active' and then the boolean answer of this will be done OR with status = 'past due'

While in 

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

First OR will be done and then And will be done... Thats y this will give the different answer

Bhavik Goyal
  • 2,786
  • 6
  • 23
  • 42
2

The MySQL manual has a page on operator precedence. It shows that AND has a higher precedence. So p1 AND p2 OR p3 is interpreted as (p1 AND p2) OR p3. A truth table will easily convince you that this is different from p1 AND (p2 OR p3).

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
1

It is not special about SQL, it is about boolean logic. Your 1st statement is:

a AND b OR c

That means if any c is true, you get the row.

In a AND (b OR c)

you get only rows where a is true and (b or c) is true.

YvesR
  • 5,922
  • 6
  • 43
  • 70
1

You are changing the condition when you are adding the parentheses.

In the initial case the result set consists of those records which satisfy the conditions 

`date_next_payment` <= '2011-02-02' AND `status` = 'active'

or the condition 

 `status` = 'past due'.

After you add the parentheses the result set will consist of those records which satisfy the condition 

`date_next_payment` <= '2011-02-02'

and any of the conditions 

`status` = 'active'

or

`status` = 'past due'
gsk
  • 1,685
  • 1
  • 13
  • 19
0

In your first query status = 'active' is condition false then not evalute next status column condition(status )

in Your second query

parentheses () have high priority

. So (status = 'active' OR status = 'past due') condition evaluate first.

Manish Trivedi
  • 3,481
  • 5
  • 23
  • 29
0

It's because when you are not using the parentheses you actually saying :

`date_next_payment` <= '2011-02-02'     AND `status` = 'active'
OR
`status` = 'past due'.

Which means that when status` = 'past due' this record would be shown too. regarless of passing the other conditions.

But when you do use parentheses you require:

 `date_next_payment` <= '2011-02-02'

AND to pass one of the two other conditions.

Hagai L
  • 1,593
  • 1
  • 18
  • 40