Left Join返回行数少于左表查询结果？问题排查

问题根源 #

你在LEFT OUTER JOIN后的WHERE子句中加入了对右表position_data4.hour的过滤条件：

AND position_data4.hour >= 1677628800 
AND position_data4.hour <= 1677632400

当左表的行在右表中找不到匹配记录时，右表的所有字段（包括hour）都会返回NULL。而NULL参与比较运算时结果为FALSE，这部分行就会被WHERE条件直接过滤掉，最终导致结果行数远少于左表的查询结果。

修正方案 #

要保留左表所有符合条件的行，需将对右表的过滤条件移至ON子句中，而非WHERE子句：

SELECT 
  COUNT (*) 
FROM 
  state_vectors_data4 
  LEFT OUTER JOIN position_data4 ON (
    state_vectors_data4.lastcontact = position_data4.maxtime 
    AND state_vectors_data4.icao24 = position_data4.icao24
    AND position_data4.hour >= 1677628800 
    AND position_data4.hour <= 1677632400
  )
WHERE 
  state_vectors_data4.time >= 1677628800 
  AND state_vectors_data4.time < 1677628810
  AND state_vectors_data4.hour >= 1677628800 
  AND state_vectors_data4.hour <= 1677632400 
  AND state_vectors_data4.lat > 22.707434526056968 
  AND state_vectors_data4.lat < 71.83985097021925 
  AND state_vectors_data4.lon < -49.97631795370285 
  AND state_vectors_data4.lon > -168.4528775341493;

修改后，右表的过滤条件会在连接阶段生效，左表中没有匹配右表条件的行依然会被保留（右表字段显示为NULL），最终计数结果会和单独查询左表的结果一致。

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