LEFT JOIN加WHERE筛选machinery_id后丢失分类的问题求助

嗨，我完全懂你的困扰——本来想保留所有分类数据，同时只关联指定machinery_id=5的机器分类记录，结果加了WHERE子句后分类全没了对吧？这其实是左连接执行逻辑导致的小坑，咱们来快速解决它！

问题根源 #

你原来的语句里，WHERE machinery_id = 5是在左连接完成之后执行的过滤操作。左连接原本会保留Categories的所有行，但那些没有匹配到Machinery_Categories的行，machinery_id字段会是NULL。而NULL = 5的判断结果是不成立的，所以这些行就被WHERE子句直接过滤掉了，最后只剩下能匹配到machinery_id=5的记录。

最优解决方案：把过滤条件移到JOIN的ON子句里 #

正确的做法是将machinery_id = 5的条件放到左连接的ON子句中，这样在连接过程中就只筛选Machinery_Categories中符合条件的行，同时完整保留Categories的所有记录：

SELECT * 
FROM Categories 
LEFT JOIN Machinery_Categories 
  ON Categories.category_id = Machinery_Categories.m_category_id 
  AND Machinery_Categories.machinery_id = 5 
ORDER BY ordering ASC

为什么这样有效？ #

• ON子句是在连接过程中应用条件：它只会控制右表（Machinery_Categories）哪些行会被匹配到左表，左表的所有行都会被保留下来——哪怕没有匹配到右表符合条件的行，对应的右表字段会显示为NULL。
• 而WHERE子句是在连接完成后过滤整个结果集，这时候会把所有machinery_id不等于5（包括NULL）的行都去掉，这就是你之前丢失分类数据的核心原因。

如果出于某些特殊原因你一定要用WHERE子句，也可以这样写，但不如上面的方法优雅：

SELECT * 
FROM Categories 
LEFT JOIN Machinery_Categories 
  ON Categories.category_id = Machinery_Categories.m_category_id 
WHERE Machinery_Categories.machinery_id = 5 
   OR Machinery_Categories.machinery_id IS NULL
ORDER BY ordering ASC

不过这种写法要注意：如果Machinery_Categories本身存在machinery_id为NULL的行，这些行也会被包含进来，可能不符合你的实际需求，所以更推荐第一种方法。

内容的提问来源于stack exchange，提问作者Skidpanda