LEFT JOIN中ON与WHERE子句条件差异及结果不同原因咨询

这两个查询的核心差异来自LEFT JOIN条件和WHERE条件的执行时机不同，具体拆解如下：

第一条查询（条件在JOIN子句） #

SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id AND e.start BETWEEN ? AND ?
GROUP BY t.name;

这条SQL的逻辑是：

• 先执行左关联：以event_type的所有记录为基础，只关联event中既匹配type_id又符合时间范围的记录
• 对于event_type中没有找到符合条件event的条目，e.id会是NULL，COUNT(e.id)会统计为0
• 最终结果会包含所有event_type的名称，对应的计数是0或符合条件的事件数量

第二条查询（条件在WHERE子句） #

SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id
WHERE e.start BETWEEN ? AND ?
GROUP BY t.name;

这条SQL的逻辑是：

• 先执行左关联：以event_type的所有记录为基础，关联所有匹配type_id的event记录（不管时间范围）
• 关联完成后，通过WHERE子句过滤**e.start符合时间范围**的记录
• 注意：LEFT JOIN后，没有匹配到event的event_type条目里，e.start是NULL，而NULL和任何值做BETWEEN比较都会返回UNKNOWN，WHERE子句只会保留结果为TRUE的记录，所以这些没有匹配的event_type条目会被直接过滤掉
• 最终结果只会包含有符合时间范围事件的event_type名称，没有对应事件的类型会被排除

直观示例 #

假设event_type有3条记录：会议、培训、团建

• 会议有2条符合时间的事件，培训没有符合时间的事件，团建有1条符合时间的事件
• 第一条查询返回：会议(2)、培训(0)、团建(1)
• 第二条查询返回：会议(2)、团建(1)（培训被过滤）

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