使用LEFT JOIN在MySQL中查找孤立记录

Question

我试图使用LEFT JOINS查找不同的孤立menu_item记录，然后检查NULL值来确认。下面的查询结果并非真正的孤立。mitem_id是menu_item表的主键，在price_info表中是外键。pinfo_id是price_info表的主键，在orderdetails表中是外键。

SELECT DISTINCT mi.mitem_id, descr 
FROM menu_item mi 
    LEFT JOIN price_info pi ON pi.menu_item = mi.mitem_id 
    LEFT JOIN orderdetails od ON od.price_info = pi.pinfo_id 
WHERE od.detail_id IS NULL;

返回的记录之一如下:

+----------+------------------+
| mitem_id | descr            |
+----------+------------------+
|      926 | Alameda Pale Ale |
+----------+------------------+

我知道这不是一个孤立节点，因为当我运行查询时：

SELECT od.detail_id 
FROM orderdetails od 
   INNER JOIN price_info pi ON pi.pinfo_id = od.price_info 
    INNER JOIN menu_item mi ON mi.mitem_id = pi.menu_item 
WHERE mi.mitem_id = 926;

它返回一组记录：

+-----------+
| detail_id |
+-----------+
|    142830 |
|    142876 |
|    143394 |
|    143610 |

为什么它返回的结果不是孤儿？

- Matt Lampe

---

LEFT JOIN 返回所有在“左”表中的条目，这些条目可能也在“右”表中，也可能不在。它仍然会返回在“右”表中的记录，因为它们也在“左”表中。 - Ryan J

如果您正在使用OUTER JOIN，则JOIN的顺序对结果很重要。您的查询非常不同。您应该为其他表添加最小样本数据。 - VMai

联接操作是否可能导致orderdetails返回至少一行detail_id为NULL的结果？也就是说，可能有多行orderdetails与menu_item和price_info匹配，其中至少一行不为NULL。 - kane

Answer 1

Kane的正确之处在于，返回的记录并非空值。由于menu_item和price_info之间存在多对多关系，我无法使用LEFT JOIN WHERE NULL来查找孤立的记录。最终，我不得不修改查询语句为：

SELECT DISTINCT mi.mitem_id, descr FROM menu_item mi
WHERE mi.mitem_id NOT IN 
(SELECT mi.mitem_id FROM menu_item mi 
INNER JOIN price_info pi ON pi.menu_item = mi.mitem_id 
INNER JOIN orderdetails od ON od.price_info = pi.pinfo_id)

- Matt Lampe