NOT IN不完全等价于NOT EXISTS

来源：互联网发布：布什家族知乎编辑：程序博客网时间：2024/05/22 10:50

很多数据库对NOT IN的处理很慢，网上很多文章都建议使用NOT EXISTS来替代，而NOT EXISTS又可以通过left join来替代。例如：select * from t1 where not exists (select 1 from (select c1 from t2) zz where zz.c1=t1.c1)可以写成select t1.* from t1 left join t2 on t1.c1=t2.c1 and t2.c1 is null.但是，事实上NOT IN并不完全等价于NOT EXISTS。

NOT IN和NOT EXISTS的区别主要在于对null的处理。考虑一下SQL语句

NOT IN：select * from t1 where c1 not in (select c1 from t2)；

NOT EXISTS：select * from t1 where not exists (select 1 from (select c1 from t2) zz where zz.c1=t1.c1)；

对于表t2的c1有null的情况，NOT IN查询是没有结果的，而NOT EXISTS不受影响；

对于表t1的c1有null的情况，NOT IN会将null过滤掉，而NOT EXISTS则不会；

所以，NOT IN正确写成NOT EXISTS应该是：

select * from t1 where not exists (select 1 from (select c1 from t2) aa where aa.c1=t1.c1) and

not exists (select 1 from (select c1 from t2) bb where bb.c1 is null) and

t1.c1 is not null

对于t1或t2的c1明确为NOT NULL的情况，可以退化。

可参考：http://stackoverflow.com/questions/173041/not-in-vs-not-exists

0 0