mysql - 有人可以解释为什么这个左外连接会这样吗？

Question

我有两个表，item和item_popularity并且需要为作为输入提供的少数项目找到那些不受欢迎的项目。我想了一个 LEFT OUTER JOIN与 NULL之后检查是正确的方法，但我一直在采取一些非常奇怪的行为。然后我稍微修改了查询并让它工作，但我真的很想了解为什么它会这样。

假设这些表具有以下值:

 item
+----+----------+
| id | name     |
+----+----------+
| 1  | Item 1   |
| 2  | Item 2   |
| 3  | Item 3   |
| 4  | Item 4   |
| 5  | Item 5   |
+----+----------+

item_popularity
+---------+-------------+----------+
| id_item | popularity  | id_store |
+---------+-------------+----------+
| 1       | 0.78        | 1        |
| 3       | 0.23        | 1        |
| 4       | 0.6765      | 1        |
+----+------------------+----------+

我原来的查询如下:

SELECT item.id, item.name, item_popularity.popularity FROM item
LEFT OUTER JOIN item_popularity
ON item.id = item_popularity.id_item
WHERE item.id IN (provided input)
AND id_store = (provided store)
AND item_popularity.id_item IS NULL

但是，这不会返回任何项目，因为它只加入受欢迎的项目，如 on this fiddle 所示。 。我用谷歌搜索了一下，发现很多人都有同样的问题，到处都有人说要移动 WHERE 的第一部分。 JOIN 的条款，因为这样会导致 LEFT JOIN成为INNER JOIN或类似的东西。

所以我这样做了，然后查询变成了:

SELECT item.id, item.name, item_popularity.popularity FROM item
LEFT OUTER JOIN item_popularity
ON item.id = item_popularity.id_item
AND item.id IN (provided input)
AND id_store = (provided store)
WHERE item_popularity.id_item IS NULL

现在到了奇怪的地方。假设我正在寻找项目 2、3 和 5。我知道只有 3 有受欢迎程度得分，所以我应该看到结果是 2 和 5，对吧？嗯，有点像。我在那里看到 2 和 5，但结果中还列出了 1 和 4! Here's the fiddle .

好吧，所以我设法获得正确结果的唯一方法是在 WHERE 中重复 id 过滤器。子句，使查询看起来很难看，如 here 所示。 :

SELECT item.id, item.name, item_popularity.popularity FROM item
LEFT OUTER JOIN item_popularity
ON item.id = item_popularity.id_item
AND item.id IN (provided input)
AND id_store = (provided store)
WHERE item.id IN (provided input)
AND item_popularity.id_item IS NULL

我不知道是否应该重复 id_store过滤WHERE ，我可能只是为了确定性。谁能解释为什么会发生这种情况以及有更好的方法来解决它吗？

Answer 1

您的第一个查询不起作用的原因不是因为左表上的条件，而是因为您在右表上有条件。在 fiddle 中完全删除 WHERE 子句，但选择要使用的所有字段(只是将它们视为调试练习)。因此，使用:

SELECT item.id, item.name, item_popularity.popularity, id_store 
FROM item
LEFT OUTER JOIN item_popularity
ON item.id = item_popularity.id_item

执行此操作后，您将看到条件 item_popularity.id_store = 1 将排除您看到的被排除的记录。