二十七、IN与EXISTS对SQL优化的影响

              IN与EXISTS对SQL优化的影响

select * from v$version;

drop table emp purge;
drop table dept purge;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
set timing on 
set linesize 1000
set autotrace traceonly explain

--这三种写法代价一样
select * from dept where deptno NOT IN ( select deptno from emp ) ;
select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;
select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;

在ORACLE11g中，IN才用的是anti算法，EXISTS才用的也是anti算法，所以代价一样。

在ORACLE10g中，IN才用的是anti算法，EXISTS才用的filter算法，所以代价不一样。