not in 与not exists 区别、以及效率分析

大家好，欢迎来到IT知识分享网。

前言

因为 not in 和 not exists 涉及到子查询和关联子查询，所以先了解一下子查询和关联子查询以及它们之间的区别

子查询与关联子查询

子查询

非相关子查询是独立于外部查询的子查询，子查询执行完毕后将值传递给外部查询

 select * from emp where sal = (select max(sal) from emp); 

执行逻辑：

1. 先执行内层 sql
2. 后执行外层 sql

关联子查询

在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。

select * from dept d where exists(select * from emp e where e.deptno = d.deptno); 

执行逻辑

1. 先执行外层 sql
2. 后执行内层 sql

测试案例1 not in 与 not exits 速度对比

本质上考察子查询与关联查询的速度对比

创建表

create table A( a varchar2(10), b varchar2(10), c varchar2(10) ); create table B( a varchar2(10), b varchar2(10) ); truncate table A; truncate table B; select * from A; select * from B; 

生成测试数据

begin for i in 0 ..  loop if mod(i, 2) = 0 then insert into A values(i, i+2， i); else insert into A values(i, i， i); end if; insert into B values(i, i); end loop; commit; end; 

Tips: mod(被除数， 除数) 作用： 取余

查询数据

-- not exists 方式 select c from a where not exists( select c from b where (a.a = b.a and a.b = b.b) ) select c from a where not exists( select 1 from b where (a.a = b.a and a.b = b.b) ) -- not in 方式 select c from a where c not in ( select c from a join b on (a.a = b.a and a.b = b.b) ) 

测试案例2- not in 存在逻辑问题

truncate table t1; truncate table t2; create table t1(c1 int,c2 int); create table t2(c1 int,c2 int); insert into t1 values(1,2); insert into t1 values(1,3); insert into t2 values(1,2); insert into t2 values(1,null); commit; select * from t1 where c2 not in(select c2 from t2);　　-->执行结果：无 select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2)　　-->执行结果：1　　3 

not exists 执行计划分析

not in 执行计划分析

not in 和 not exists 执行逻辑

-- 执行逻辑: not in  for i in (select * from t1) loop for j in( select * from t2) loop if( t1[c2] != t2[c2]) then output_record end if; end loop; end loop; -- 执行逻辑: not exists for i in (select * from t1) loop if( not exists(select 1 from t1 where t2.c2 = t1.i[c2])) then output_record end if end loop; 

总结：如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

not in 不对 null 做处理

select 1 from dual where null in (0,1,2,null) 

参考