in/exists和not in/not exists执行效率

最新推荐文章于 2025-06-28 21:29:29 发布

转载最新推荐文章于 2025-06-28 21:29:29 发布 · 75 阅读

本文深入探讨了SQL查询中IN与EXISTS的区别与应用场景，包括它们的执行流程、适用条件和性能考量。同时，还介绍了NOTIN与NOTEXISTS的用法及其与IN/EXISTS的不同之处，提供了实用的SQL查询优化建议。

http://www.xifenfei.com/2176.html

http://blog.youkuaiyun.com/maoweiting19910402/article/details/8363979

一、IN 与EXISTS
1、理解
IN的执行流程
SELECT * FROM T1 WHERE X IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X = T2.Y
从这里可以看出，IN需要先处理T2表，然后再和T1进行关联

EXISTS的执行流程

   
         SELECT * FROM T1 WHERE EXISTS (SELECT NULL FROM T2 WHERE Y = X)
        
         --可以理解为：
        
         for x in ( select * from t1 ) LOOP
        
             if ( exists ( select null from t2 where y = x.x )THEN
        
                 OUTPUT THE RECORD
        
             end if
        
         end loop

从这里看出，EXISXTS会先查询T1表，然后再LOOP处理T2表

2、结论
对于in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。
综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

二、NOT IN 与NOT EXISTS
1、理解
NOT IN的执行流程
SELECT * FROM T1 WHERE X NOT IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X != T2.Y

NOT EXISTS的执行流程

   
         SELECT .. .. .
        
           FROM ROLLUP R
        
          WHERE NOT EXISTS
        
          (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
        
         --可以理解为:
        
         for x in ( select * from rollup )
        
                loop
        
                    if ( not exists ( that query ) ) then
        
                           OUTPUT
        
                    end if;
        
                 end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。具体见:in/exists和not in/not exists语意探讨

2、结论
not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null.一般情况下建议使用not exists

   
         --比如:
        
         SELECT .. .. ..
        
           FROM ROLLUP R
        
          WHERE NOT EXISTS
        
          (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
        
         --改成
        
         SELECT .. .. ..
        
           FROM TITLE T, ROLLUP R
        
          WHERE R.SOURCE_ID = T.TITLE_ID(+)
        
            AND T.TITLE_ID IS NULL;
        
         --或者
        
         SELECT /*+ HASH_AJ */
        
         .. .. ..
        
           FROM ROLLUP R
        
          WHERE OURCE_ID NOT IN
        
                (SELECT OURCE_ID FROM TITLE T WHERE OURCE_ID IS NOT NULL);