测试数据

20:25:52[test](;)> select * from t; +------+------+ | id   | b    | +------+------+ |    1 | NULL | |    2 |    1 | |    3 |    4 | +------+------+ 3 rows in set (0.00 sec)

实现查询t表中id 的值不存在b中的id记录，有三种实现方式，分别为left join、not exists、not in

其中前两种对null值的处理机制相同

20:37:02[test](;)> select t.id,t.b from t left join t tmp on t.id=tmp.b where tmp.b is null; +------+------+| id   | b    |+------+------+|    2 |    1 ||    3 |    4 |+------+------+2 rows in set (0.00 sec)20:37:11[test](;)> select id,b from t tmp where not exists(select 1 from t where tmp.id=t.b);+------+------+| id   | b    |+------+------+|    2 |    1 ||    3 |    4 |+------+------+2 rows in set (0.01 sec)

但是对于not in 结果不尽相同

20:38:11[test](;)> select id,b from t where id not in (select b from t);              Empty set (0.01 sec)

子查询中的结果集中如果包含null则返回null，无值返回，可以过滤掉null值达到预期的效果

20:38:37[test](;)> select id,b from t where id not in (select b from t where t.b is not null); +------+------+ | id   | b    | +------+------+ |    2 |    1 | |    3 |    4 | +------+------+ 2 rows in set (0.00 sec)

关于in如果条件字段和子查询中查询的字段对应都是null值，则返回null，如

21:47:51[test](;)> select * from t;+------+------+| id   | b    |+------+------+|    1 | NULL ||    2 |    1 ||    3 |    4 || NULL | NULL |+------+------+4 rows in set (0.00 sec)21:47:52[test](;)> select id,b from t where id in (select b from t);+------+------+| id   | b    |+------+------+|    1 | NULL |+------+------+1 row in set (0.00 sec)

无法匹配出id为null的记录

对于执行的效率，left join比not exists稍快，not in效率最低，是最应该避免的

参考文献：https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/