MySQL 8中LEFT JOIN的WHERE与ON子句加IS NULL为何结果不同？

代码示例 #

-- pattern1
SELECT count(a.value)
FROM A AS a
LEFT JOIN B AS b
ON a.id = b.id
WHERE b.value IS NULL
;

-- pattern2
SELECT count(a.value)
FROM A AS a
LEFT JOIN B AS b
ON a.id = b.id
AND b.value IS NULL
;

为什么结果不一样？ #

核心原因是ON子句和WHERE子句在LEFT JOIN中的作用时机完全不同：

Pattern 1的执行逻辑 #

1. 先执行LEFT JOIN B ON a.id = b.id：这一步会保留左表A的所有4000条记录，A中能匹配到B的记录会带上B的字段值，匹配不到的B字段全为NULL。
2. 再执行WHERE b.value IS NULL：这是对JOIN后的整个结果集做过滤，所有「匹配到B且b.value不为NULL」的记录都会被删掉，最终剩下的只有A中要么完全没匹配到B，要么匹配到了但b.value本身是NULL的记录，所以count结果是3549。

Pattern 2的执行逻辑 #

1. 执行LEFT JOIN B ON a.id = b.id AND b.value IS NULL：这里的条件是只匹配B中id和A一致且b.value为NULL的记录。对于A中那些在B里有匹配但b.value不为NULL的记录，以及完全没匹配到B的记录，都会保留A的原始数据，B字段置为NULL。
2. 因为是LEFT JOIN，左表A的所有4000条记录全程不会被过滤，只要A的value字段不为空（题目默认如此），count(a.value)统计的就是A的总记录数，所以结果是4000。

关键区别点 #

• ON子句是连接阶段的匹配规则，只决定哪些B表数据能和A表关联，不会剔除左表的任何记录；
• WHERE子句是连接完成后的过滤规则，会直接删掉整个结果集中不符合条件的记录。

内容的提问来源于stack exchange，提问作者上西潤