mysql - MYSQL:WHERE IN和OR不返回相同的行

我是mysql的新手，正在玩sakila数据库。谁能暗示为什么这些查询不返回完全相同的行？

如果我使用一系列OR语句：

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)

如果我使用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)

似乎IN（）仅列出city_id的第一个实例。在网络上的任何地方都表明这两种情况仅在性能方面有所不同。我想念什么？

最佳答案

您需要在第一个查询中将AND OR组合适当地包含在round brackets ()中。

子句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将返回所有store_id为1和city_id为1的行以及所有city_id为41或312或459的行，而与store_id无关。

将其更改为以下查询，它将返回与第二个IN查询完全相同的行。

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

关于mysql - MYSQL:WHERE IN和OR不返回相同的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47552962/