浅谈 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
)