背景
2020年03月01号 ,天气晴,天空无云且周末,白天的我像一只撒欢的小毛驴(满屋子的蹦跶).晚上10点20分的我已经洗漱好了,准备早起早睡迎接下明天的周一.叮铃铃~叮铃铃(真实的铃声还是很好听的,我模仿不出来),领导来电话了,说让导出几个(差一个到两位数)数据,为了走向人生巅峰(恰饭)我当然不能拒绝了 .于是我拍着我并不结实的胸膛应承了下来 .
过程
挂了电话,我就着手写SQL了.因为有好几个数据是要查询排除某个数据之后的其他数据(需要求两个表的差集),而且因为这个功能刚上线不久数据量不大,本着省事的原则我用的not in 配合子查询 .结果一执行 ,我特么狗眼都掉出来了 0 ,count(1)给我返回了一个0.百思不得其姐 ,这种时候我也不能换个方式查询了,先把这个问题搞明白吧 ,至于领导那就暂时晾一下吧 . 类似于 :
select COUNT(1) FROM a where a.id not in (select aid from b);
解决
咋解决啊?遇到事情不要慌 ,一输入二搜索,度娘使你精神爽 .搜了下,可能是因为b 表的 aid 有null值导致的(如果真的有null值,那第二天同事可能改一天BUG了).一看果然特么的有null值!那啷个办咧?生产数据咱也不敢咋样 ,换 NOT EXISTS先把数据导出来再说,毕竟咱也不能让领导等久了,咱还要恰饭的不是 ?
于是 ,类似于 :
SELECT COUNT(1) FROM a where not EXISTS(
SELECT *
FROM b
WHERE b.`aid`= a.id)
原因
拿上一个栗子举个栗 : NOT IN 的实现原理是 对每一个a表的id字段和每一个b表的aid字段进行不相等比较(!=).而SQL中 任意!=null的运算结果都是false.所以B表中存在一个 null ,NOT IN的查询永远都会返回false,即查询结果为空.
结论
当我们不确定某表的值是不是有null的情况下,又要求差集.可以先排除下null 或者使用 NOT EXISTS或者使用外连接 .
栗 :
1.NOT IN 排除null
select COUNT(1) FROM a where a.id not in (select aid from b where aid IS NOT NULL);
2.NOT EXISTS
SELECT COUNT(1) FROM a where not EXISTS(
SELECT *
FROM b
WHERE b.`aid`= a.id) ;
3.左连接配合 IS NULL
SELECT COUNT(1) FROM a LEFT JOIN a ON a.id=b.aid WHERE b.aid IS NULL
最后多嘴
当你在一个可以为NULL的列上使用 IN或者 NOT IN 子查询时，可以好好想想使用 EXISTS或 NOT EXISTS代替 .因为 nulls是 NOT IN慢的如蜗牛的罪魁祸首!
