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

前言

因为 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 .. 1000000 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 存在逻辑问题

not in	采用子查询
not exits	采用关联子查询

如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。
如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。

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) 

运行结果： 结果集为空

参考

浅谈sql中的in与not in,exists与not exists的区别以及性能分析
Oracle取余.取整
如何正确理解SQL关联子查询
关联子查询与非关联子查询的区别