大数据集LEFT OUTER JOIN丢失左表行问题排查及SQL写法咨询

我之前处理超大数据集的时候也踩过这个一模一样的坑！当你用LEFT OUTER JOIN却莫名其妙丢失左表的行，十有八九是WHERE子句的过滤条件放错地方了。

问题根源 #

LEFT JOIN的核心逻辑是：保留左表的所有行，右表能匹配上的就返回对应数据，匹配不上的字段填NULL。但如果把右表的过滤条件写在WHERE子句里，一旦右表没有匹配行，table2.var就是NULL，而NULL和任何值做比较都会返回false，结果就把左表中原本应该保留的无匹配行给过滤掉了——这就相当于把LEFT JOIN偷偷变成了INNER JOIN！

错误示例（会丢失左表行） #

比如你写的这条语句：

SELECT * FROM table1 LEFT JOIN table2 USING (IDvar) WHERE table2.var IN(val1, val2,..., val100);

这条语句只会返回左表中那些在右表有匹配且table2.var符合条件的行，完全违背了LEFT JOIN的初衷。

正确写法（完整保留左表行） #

要把右表的过滤条件放到JOIN子句里，这样过滤是在匹配阶段就完成的，不会影响左表的行保留：

SELECT * FROM table1 
LEFT JOIN table2 
  ON table1.IDvar = table2.IDvar 
  AND table2.var IN(val1, val2,..., val100);

如果习惯用USING语法，也可以把条件加在USING后面的AND里：

SELECT * FROM table1 
LEFT JOIN table2 USING (IDvar)
  AND table2.var IN(val1, val2,..., val100);

这样左表的所有行都会被保留，右表只有符合条件的行才会匹配，匹配不上的字段就显示NULL。

额外小提醒 #

如果你的数据集规模很大，这种写法还能提前过滤右表的无效数据，减少JOIN时的计算量，性能上也会更友好哦！

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