Oracle 浅谈 IN 和 EXISTS

浅谈 IN 和 EXISTS

我们在查询的时候也都用到 IN和 EXISTS ，两者在使用结果上没什么区别，都 能得到一样的结果。但是正因为效果一样，我们就需要考虑两者的性能以及差别了。
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
其实区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

IN的语法

SELECT * FROM emp WHERE job 
IN
(
SELECT job FROM emp WHERE ENAME='SMITH' 
OR ename='WARD'
)

Exists 的语法

select * from emp e1 where exists( select  '1' from emp e2 where (ename='SMITH' OR ename='WARD')  AND e1.job=e2.job
)

如果子查询得出的结果集几率比较少，主查询中的表较大且又有索引时我们应该选用in ,反之如果外层的主查询记录较少，子查询的表大，又有索引时使用exists

NOT IN 和 NOT EXISTS 的区别：
如果查询语句中使用了NOT IN 那内外表都会进行全表扫描，没有用到索引。
而 NOT EXISTS 的子查询依然能用到表上的索引。所以无论哪个表大，用NOT EXISTS 都比NOT IN要快。

NOT IN 的语法:

SELECT * FROM  emp e1 WHERE e1.deptno NOT IN
(
SELECT deptno FROM dept WHERE dname LIKE '%C%'
)

NOT EXISTS的语法：

SELECT * FROM  emp e1 WHERE NOT EXISTS
(
SELECT 'X' FROM dept d1 WHERE d1.dname LIKE '%C%'
AND d1.deptno = e1.deptno
)