oracle中in和exists、not in和not exists的用法与效率

in和exists

首先，看两个例子
1) select * from A where A.id in(select id from B)
2) select * from A where exists(select 1 from B where id=A.id)
先说说效率，当A表的数据远远大于B表的时候，则1)句效率比较高；反之，当B表的数据远远大于A表的时候，则2)句效率表较高；
这是因为，in是把外表和内标当做hash join，而exists是对外表做loop，每次loop再对内表进行查询：
再回到前面说的例子，当使用in时，先去查出B表中的数据，然后保存在缓存中，再去查出A表中满足的数据，因为要遍历整个B表，所以适合B表数据比较少的查询；
而exists(×××)只在乎括号里的数据能不能查出来，如果存在，则2)句的where语句成立，所以exists先要查出A表中的所有数据，再去在子查询中看是否满足，所以exists要遍历整个A表，则适合A表数据比较少的查询。

当两个表的数据差不多时，in和exists的查询效率是差不多的。

not in和not exists

not in在逻辑上并不完全等于not exists，如果误用not in有可能会导致致命的BUG

看一个例子

         A     id  name         B     id   name

                 1     a                     1       a

                 2     b                              c

                 3     c                      2      c

1)select * from A where A.id not in(select id from B)

no rows found

2)select * from A where not exists(select id from B where id=A.id)  

         id     name

         1       a

         2       b

正如上面所看到的的，not in出现了不期望的结果集。这是因为使用not in，如果子查询中返回任意一条记录中含有空值，则查询不返回任何记录，所以除非有限制不能有空值，尽量用not exists；

还有一个区别，使用not in，内外表都会进行全表扫描，不会使用索引，而not exists会用到子查询的索引，所以无论哪个表大，not exists的效率都会比not in要快。

因此，尽量不要使用not in。