inner join 和 exists 效率_一个in、exists、join的简单测试

创建两张表先单独插入两条数据

然后批量插入部门号为10,20,30,40的数据各10499099条

然后dept表也插些干扰数据

测试语句

开始验证in和exists和join
先比较个占比多的部门，再比较占比少的
1、 in 占比多
select count(*) from scott.EMP_TEST e where e.deptno in (select d.deptno from scott.DEPT_TEST d where d.dname='SALES') ;

跟真实执行计划基本一样，所以之后的都用autotrace来看
2.exists 占比多
select count(*) from scott.EMP_TEST e where exists (select d.deptno from scott.DEPT_TEST d where d.dname='SALES' and e.deptno=d.deptno) ;

3.join 占比多
select count(*) from scott.EMP_TEST e inner join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname='SALES' ;

http://4.in 占比少
select count(*) from scott.EMP_TEST e where e.deptno in (select d.deptno from scott.DEPT_TEST d where d.dname='TEST') ;

5.exists 占比少
select count(*) from scott.EMP_TEST e where exists (select d.deptno from scott.DEPT_TEST d where d.dname='TEST' and e.deptno=d.deptno) ;

6.join 占比少
select count(*) from scott.EMP_TEST e inner join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname='TEST' ;

7.not in 占比多
select count(*) from scott.EMP_TEST e where e.deptno not in (select d.deptno from scott.DEPT_TEST d where d.dname<>'SALES') ;

8.not exists 占比多
select count(*) from scott.EMP_TEST e where not exists (select d.deptno from scott.DEPT_TEST d where d.dname<>'SALES' and e.deptno=d.deptno) ;

9.join 占比多
select count(*) from scott.EMP_TEST e left join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname<>'SALES' where d.deptno is null ;

10.not in 占比少
select count(*) from scott.EMP_TEST e where e.deptno not in (select d.deptno from scott.DEPT_TEST d where d.dname<>'TEST') ;

11. not exists 占比少
select count(*) from scott.EMP_TEST e where not exists (select d.deptno from scott.DEPT_TEST d where d.dname<>'TEST' and e.deptno=d.deptno) ;

12. join 占比少
select count(*) from scott.EMP_TEST e left join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname<>'TEST' where d.deptno is null ;

下面创建两个简单索引来测试下

1、 in 占比多
select count(*) from scott.EMP_TEST e where e.deptno in (select d.deptno from scott.DEPT_TEST d where d.dname='SALES') ;

分析不走索引的原因是因为统计信息不全，收集下统计信息

2.exists 占比多
select count(*) from scott.EMP_TEST e where exists (select d.deptno from scott.DEPT_TEST d where d.dname='SALES' and e.deptno=d.deptno) ;

3.join 占比多
select count(*) from scott.EMP_TEST e inner join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname='SALES' ;

http://4.in 占比少
select count(*) from scott.EMP_TEST e where e.deptno in (select d.deptno from scott.DEPT_TEST d where d.dname='TEST') ;

5.exists 占比少
select count(*) from scott.EMP_TEST e where exists (select d.deptno from scott.DEPT_TEST d where d.dname='TEST' and e.deptno=d.deptno) ;

6.join 占比少
select count(*) from scott.EMP_TEST e inner join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname='TEST' ;

7.not in 占比多
select count(*) from scott.EMP_TEST e where e.deptno not in (select d.deptno from scott.DEPT_TEST d where d.dname<>'SALES') ;

8.not exists 占比多
select count(*) from scott.EMP_TEST e where not exists (select d.deptno from scott.DEPT_TEST d where d.dname<>'SALES' and e.deptno=d.deptno) ;

9.join 占比多
select count(*) from scott.EMP_TEST e left join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname<>'SALES' where d.deptno is null ;

10.not in 占比少
select count(*) from scott.EMP_TEST e where e.deptno not in (select d.deptno from scott.DEPT_TEST d where d.dname<>'TEST') ;

11. not exists 占比少
select count(*) from scott.EMP_TEST e where not exists (select d.deptno from scott.DEPT_TEST d where d.dname<>'TEST' and e.deptno=d.deptno) ;

12. join 占比少
select count(*) from scott.EMP_TEST e left join scott.DEPT_TEST d on e.deptno=d.deptno and d.dname<>'TEST' where d.deptno is null ;

在简单比较下时间

可以看出在相同条件下，执行计划是相同的，时间消耗也是一样的
因为有朋友说not exists的子查询会走索引not in索引失效，所以not exists会快，所以为了说服他又做了点补充

并没有什么差别。而且not in和not exists都会使索引失效，但是不影响子查询的索引使用！！！！所以直接说谁比谁快的都是不负责任的说法，还是要具体情况具体分析。分情况使用。那些说not exists比not in快的，这种情况很多，因为不同的原因造成他们的sql的执行计划不同了，所以效率也不同了

抛开执行计划和实际情景，不考虑数据情况，数据量、索引情况甚至统计信息等这些因素，直接说哪一种比较快都是不靠谱的