MySQL LEFT JOIN场景下关联全不匹配时的查询WHERE条件优化

之前的写法之所以漏数据，根源是关联表的过滤条件放错了子句位置：
LEFT JOIN的逻辑是先保留左表（publications）的所有行，再逐行去右表（issues）匹配关联数据，未匹配的位置会填充NULL。而WHERE子句是在整个关联流程结束后，对全量结果集逐行做筛选，不满足条件的行会被直接剔除。
当某出版物的所有关联期次都被标记为删除时，关联得到的行里i.deleted的值为1，既不满足i.deleted=0，也不是NULL，直接会被WHERE条件过滤掉，最终导致本该保留的出版物行丢失。

针对issues表的未删除过滤，要放到LEFT JOIN的ON匹配条件中——ON条件的过滤是在关联匹配阶段生效，只会决定哪些issues行能和出版物关联上，完全不会动左表本身的行数据，从根本上保证所有出版物都能被保留。

优化后的SQL：

SELECT p.id
     , p.title
     , COUNT(i.id) AS countOfIssues 
  FROM publications p 
  LEFT JOIN issues i 
    ON p.id = i.publicationId 
   AND i.deleted = 0 -- 未删除的过滤条件放在关联匹配阶段
 GROUP BY p.id;

这种写法的逻辑可以覆盖所有业务场景：

• 出版物无任何关联期次：无符合条件的issues匹配上来，结果保留该出版物，统计值为0
• 出版物同时包含已删除、未删除的关联期次：仅将未删除的期次纳入统计，结果保留该出版物，统计值为未删除期次的总数
• 出版物的所有关联期次都被标记删除：无符合条件的issues匹配上来，结果保留该出版物，统计值为0

等价实现：也可以先对issues表做过滤，再做左关联，逻辑更直观，性能和写法上没有本质差异：

SELECT p.id
     , p.title
     , COUNT(i.id) AS countOfIssues 
  FROM publications p 
  LEFT JOIN (
      SELECT id, publicationId 
      FROM issues 
      WHERE deleted = 0
  ) i ON p.id = i.publicationId
 GROUP BY p.id;

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