LEFT JOIN中ON子句加AND与WHERE子句的查询结果差异原因

核心原因：执行时机与作用范围不同 #

• 带WHERE子句的查询为何不符合预期？
  执行流程分为两步：

  1. 先执行a left join b on a.b_id = b.id：这一步会保留表A的所有行，同时关联表B中所有id匹配的行（不管arbitrary_binary_value是0还是1），没有匹配的行则表B字段填充为NULL。
  2. 再执行where b.arbitrary_binary_value = 0：此时会过滤掉所有b.arbitrary_binary_value不等于0的行——包括表B字段为NULL的行（因为NULL = 0的判断结果为假）。如果表A中有行对应的表B里没有arbitrary_binary_value=0的记录，或者根本没有匹配的表B行，这些表A的行都会被过滤掉，最终结果行数自然少于表A。

  对应SQL：

  select * from a left join b on a.b_id = b.id
  where b.arbitrary_binary_value = 0
  

• 带AND的ON条件为何符合预期？
  这里的b.arbitrary_binary_value = 0是连接匹配的一部分，执行时直接在关联阶段就只筛选表B中符合条件的行。对于表A中没有对应符合条件的表B行的记录，依然会完整保留，只是表B的字段填充为NULL。这就保证了结果集完全包含表A的所有行，行数与表A一致。

  对应SQL：

  select * from a left join b on a.b_id = b.id
  AND b.arbitrary_binary_value = 0
  

一句话总结 #

左连接的ON条件用来限定要关联的表B数据，不会丢弃表A的行；而WHERE条件是对连接后的整个结果集做过滤，会丢弃所有不符合条件的行（包括表A中未匹配到有效表B数据的记录）。

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