Photoshop Question posted in
The official user guide can be found here.

SQL query (AND & OR) mix up – Photoshop

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

Tags:

2

Answers


  1. 0

    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%'
    Login or Signup to reply.
  2. 0

    The AND operator has higher precedence than OR, so your first query is equivalent to:

    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%'

    In contrast with your second query, the conditions on status_id and updated_at apply only to the rows where number LIKE 'A390%'.

    The total count over the three individual queries cannot be less than the count returned by the second query, if all queries are performed on the same data. Presumably you have made a typo somewhere. The total of the individual queries could be greater, however, if any rows’ numbers satisfy more than one of the LIKE conditions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search