EXISTS、IN、NOT EXISTS、NOT IN的区别

EXISTS、IN、NOT EXISTS、NOT IN的区别(ZT) 


EXISTS、IN、NOT EXISTS、NOT IN的区别： 


in适合内外表都很大的情况，exists适合外表结果集很小的情况。 
exists 和 in 使用一例 
=========================================================== 
今天市场报告有个sql及慢，运行需要20多分钟，如下： 
update p_container_decl cd 
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate 
where exists( 
select 1 
from ( 
select tc.decl_no,tc.goods_no 
from p_transfer_cont tc,P_AFFIRM_DO ad 
where tc.GOODS_DECL_NO = ad.DECL_NO 
and ad.DECL_NO = 'sssssssssssssssss' 
) a 
where a.decl_no = cd.decl_no 
and a.goods_no = cd.goods_no 
) 
上面涉及的3个表的记录数都不小，均在百万左右。 

exists和in的区别： 
in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。 
这样的话，in适合内外表都很大或者外表大而内表小的情况，exists适合外表结果集很小的情况。 

我们先讨论IN和EXISTS。 
    select * from t1 where x in ( select y from t2 ) 
    事实上可以理解为： 
    select * 
      from t1, ( select distinct y from t2 ) t2 
     where t1.x = t2.y; 
    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y 都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。 
    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 
    ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。 



而我目前的情况适合用in来作查询，于是我改写了sql，如下： 
update p_container_decl cd 
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate 
where (decl_no,goods_no) in 
( 
select tc.decl_no,tc.goods_no 
from p_transfer_cont tc,P_AFFIRM_DO ad 
where tc.GOODS_DECL_NO = ad.DECL_NO 
and ad.DECL_NO = ‘ssssssssssss’ 
) 

让市场人员测试，结果运行时间在1分钟内。问题解决了，看来exists和in确实是要根据表的数据量来决定使用。 

请注意not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG： 


请看下面的例子： 
create table t1 (c1 number,c2 number); 
create table t2 (c1 number,c2 number); 

insert into t1 values (1,2); 
insert into t1 values (1,3); 
insert into t2 values (1,2); 
insert into t2 values (1,null); 

select * from t1 where c2 not in (select c2 from t2); 
no rows found 
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2); 
c1 c2 
1 3 

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。 
因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。 
除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接。 


根据如下表的查询结果，那么以下语句的结果是（知识点：not in/not exists+null） 
SQL> select * from usertable; 
USERID           USERNAME 
-----------      ---------------- 
      1          user1 
      2          null 
      3          user3 
      4          null 
      5          user5 
      6          user6 
      
SQL> select * from usergrade; 
USERID         USERNAME           GRADE 
----------     ----------------   ---------- 
      1        user1              90 
      2        null               80 
      7        user7              80 
      8        user8              90 
执行语句： 
select count(*) from usergrade where username not in (select username from usertable); 

select count(*) from usergrade g where not exists 
(select null from usertable t where t.userid=g.userid and t.username=g.username); 

结果为：语句1(   )   语句2  (   ) 

A: 0     B:1     C:2     D:3      E:NULL 
.-------------------------------------------------------------------------------- 

现在终于搞清楚了，答案这里也不说了。就说说几个知识点 
1、NULL不是没有，而是不知道。在oracle排序中它代表最大的值。 

10:55:32 SQL> create table li2_tmp as select 1 a,'this is 1' b from dual; 
Table created. 
10:56:35 SQL> insert into li2_tmp (a,b) values (null,'this is null'); 
10:56:55 SQL> insert into li2_tmp (a,b) values (null,'it'' null'); 
1 row created. 
10:57:32 SQL> commit; 
Commit complete. 
10:57:37 SQL> select * from li2_tmp where a>0; 

         A B 
---------- --------- 
         1 this is 1 

Elapsed: 00:00:00.00 
10:58:02 SQL> select * from li2_tmp order by a; 

         A B 
---------- --------- 
         1 this is 1 
           it' null 

11:02:51 SQL> select * from li2_tmp where a=null; 

no rows selected 

说明：NULL不能用于比较，因为它的值谁都不知道，但在排序了，oracle 认为它是最大的值。 

2、什么东西都不在NULL中，也不在非空中 
这个理解起来很困难，还是用例子吧 
10:58:21 SQL> select count(*) from li2_tmp where a in (null); 

  COUNT(*) 
---------- 
         0 

11:02:34 SQL>  select count(*) from li2_tmp where a not in (null); 

  COUNT(*) 
---------- 
         0 

11:10:42 SQL> select * from li2_tmp where exists (select null from dual); 

         A B 
---------- --------- 
         1 this is 1 
           it' null 

Elapsed: 00:00:00.00 
11:11:16 SQL> select * from li2_tmp where not  exists (select null from dual); 

no rows selected 

11:13:42 SQL> select * from li2_tmp t1 where exists (select null from li2_tmp t2 where t1.a=t2.a) 
11:14:26 SQL> / 

         A B 
---------- --------- 

         1 this is 1 

http://cindysaj.iteye.com/blog/288761