M. of CA M. of CA - 5 months ago 9
SQL Question

SQL query (AND & OR) mix up

help me understand why i am getting different COUNTS

Query 1

SELECT Count(*)
FROM `foo`
WHERE `status_id` = 2
AND `updated_at` < Date(Now() - INTERVAL 1 day)
AND `number` LIKE 'A390%'
OR `number` LIKE 'A391%'
OR `number` LIKE 'A392%'



results = 20000


Query 2

SELECT Count(*)
FROM `foo`
WHERE `status_id` = 2
AND `updated_at` < Date(Now() - INTERVAL 1 day)
AND ( `number` LIKE 'A390%'
OR `number` LIKE 'A391%'
OR `number` LIKE 'A392%' )



results = 14967





SELECT Count(*)
FROM `foo`
WHERE `status_id` = 2
AND `updated_at` < Date(Now() - INTERVAL 1 day)
AND `number` LIKE X


running the above query for each (A390, A391 ,A392) separately gives me:


'A390%' = 0;
'A391%' = 1496;
'A392%' = 10000;



EDIT:
Adding Screenshots to make it more clear. i had to do some photoshop to change the table name

Query 1

Query 2

Query 3

Query 4

Query 5

Answer

http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

Your first query's condition is equivalent to:

 (`status_id` = 2 
   AND `updated_at` < Date(Now() - INTERVAL 1 day) 
   AND `number` LIKE 'A390%'
 )
 OR `number` LIKE 'A391%' 
 OR `number` LIKE 'A392%'
Comments