文章目录
前言
因为 not in 和 not exists 涉及到子查询和关联子查询,所以先了解一下子查询和关联子查询以及它们之间的区别
子查询与关联子查询
子查询
非相关子查询是独立于外部查询的子查询,子查询执行完毕后将值传递给外部查询
select * from emp where sal = (select max(sal) from emp);
执行逻辑:
- 先执行内层 sql
- 后执行外层 sql
关联子查询
在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并返回它的记录。然后,外部查询根据返回的记录做出决策。
select * from dept d where exists(select * from emp e where e.deptno = d.deptno);
执行逻辑
- 先执行外层 sql
- 后执行内层 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关联子查询
关联子查询与非关联子查询的区别