MySQL 排除 JOIN 上的结果

Question

我正在开发一款餐厅 CMS 应用。我在 2 个表 menu_sections 和 menu_items 之间建立了多对多关系。该关系由名为 menu_relationships 的表维护。

例如，假设名为零食(menu_section_id = 1)的菜单部分包含一个名为Pretzels(menu_item_id = 1) 和名为Desserts (menu_section_id = 2) 的菜单部分包含一个名为Ice Cream 的菜单项 ( menu_item_id = 2)，但是Ice Cream 也包含在另一个名为Kids Food (menu_section_id = 3) 的菜单部分中。因此，menu_relationships 表中将有 3 行来映射这 3 种关系。关系表如下所示:

---------------------------------------
|   menu_section_id  |  menu_item_id  |
|=====================================|
|          1         |        1       |
|-------------------------------------|
|          2         |        2       |
|-------------------------------------|
|          3         |        2       |
---------------------------------------

到目前为止一切顺利。

我想生成一个结果集，它将返回所有菜单项的名称，但具有给定 menu_section_id 的菜单项除外。因此，为了返回菜单项名称，我在 menu_items 表上进行了连接。这是 SQL:

SELECT menu_section_id, menu_items.menu_item_id, menu_item_name 
FROM menu_relationships
JOIN menu_items 
ON menu_items.menu_item_id = menu_relationships.menu_item_id
WHERE menu_section_id != 2 

结果集将为每个不包含给定 menu_section_id 的关系提供一行。使用示例数据，我将从关系表中返回 2 行:

-----------------------------------------------------------
|   menu_section_id   |  menu_item_id  |  menu_item_name  |
|======================================|==================|
|          1          |        1       |     Pretzels     |
|--------------------------------------|------------------|
|          3          |        2       |     Ice Cream    |
-----------------------------------------------------------

但我想要的是从结果集中完全排除菜单项，如果它与指定的 menu_section_id 有任何关系。换句话说，在这个例子中，我只想返回完全没有关系映射的菜单项的行到 2 的 menu_section_id，我只想返回 Pretzels 行。

我已经尝试过使用 bit_xor() 聚合函数对 GROUP BY 和 HAVING 进行各种操作，但到目前为止一点运气都没有得到我想要的。

我本可以花更少的时间来解释这一点，但我希望它尽可能清楚。我希望是这样。谁能帮忙？

Answer 1

这是使用 LEFT OUTER JOIN 的绝妙案例，因为它包含左侧表中的所有行并尽可能匹配，返回 NULL 任何不匹配。

以 Mark Breyer 上面的示例查询为基础，请参阅以下示例:

SELECT R.menu_section_id, I.menu_item_id, I.menu_item_name
FROM menu_items AS I
LEFT OUTER JOIN menu_relationships R on (R.menu_item_id=I.menu_item_id) AND (R.menu_section_id = 2)

mysql 优化器实际上可能将其重写为子查询 - 我无论如何都不是优化专家 - 我会看看你的索引构建方式，看看这种类型的连接是否对你的模式有意义.我还会测试它是否真的更快，因为它实际上语义更少。