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