MYSQL : WHERE IN and OR are not returning the same rows

Question

i'm new to mysql and playing with sakila database. Can anyone hint as to why these to queries do not return the exact same rows?

IF i use a sequence of OR statements:

mysql> use sakila
Database changed
mysql> SELECT first_name,last_name,address.city_id FROM customer
    -> JOIN store ON store.store_id=customer.store_id
    -> JOIN address ON customer.address_id=address.address_id
    -> WHERE store.store_id=1 AND address.city_id=1 OR address.city_id=42
    -> OR address.city_id=312 OR address.city_id=459;
+------------+-------------+---------+
| first_name | last_name   | city_id |
+------------+-------------+---------+
| JULIE      | SANCHEZ     |       1 |
| SCOTT      | SHELLEY     |      42 |
| CLINTON    | BUFORD      |      42 |
| MATTIE     | HOFFMAN     |     312 |
| CECIL      | VINES       |     312 |
| NELSON     | CHRISTENSON |     459 |
+------------+-------------+---------+
6 rows in set (0,00 sec)

IF i use IN ():

mysql> SELECT first_name,last_name,address.city_id FROM customer
    -> JOIN store ON store.store_id=customer.store_id
    -> JOIN address ON customer.address_id=address.address_id
    -> WHERE store.store_id=1 AND address.city_id IN (1,42,312,459);
+------------+-------------+---------+
| first_name | last_name   | city_id |
+------------+-------------+---------+
| JULIE      | SANCHEZ     |       1 |
| SCOTT      | SHELLEY     |      42 |
| CECIL      | VINES       |     312 |
| NELSON     | CHRISTENSON |     459 |
+------------+-------------+---------+
4 rows in set (0,00 sec)

It seems IN() lists only first instance of city_id. Everywhere on the web it's stated that these two scenarios differ ONLY performance-wise. What am i missing?

Answer 1

You need to enclose AND OR combinations in round brackets () appropriately in your 1st query.

The clause WHERE store.store_id=1 AND address.city_id=1 OR address.city_id=42 OR address.city_id=312 OR address.city_id=459 will return all rows with store_id as 1 and city_id as 1 plus all rows with either city_id as 41 or 312 or 459 irrespective of store_id.

Change it to below query and it will return exact same rows as 2nd IN query.

SELECT first_name,last_name,address.city_id FROM customer
JOIN store ON store.store_id=customer.store_id
JOIN address ON customer.address_id=address.address_id
WHERE store.store_id=1
AND (address.city_id=1
    OR address.city_id=42 
    OR address.city_id=312
    OR address.city_id=459);