其實(shí)這個(gè)都是子查詢，而在最新的oracle 11g中，in和exists基本不太可能產(chǎn)生變化，因?yàn)?1g的cbo不僅可以u(píng)nnest展開子查詢?yōu)楸磉B接，還新增了null-aware anti join的算法，由于in對(duì)null敏感。

而在oracle 11g之前，如果關(guān)聯(lián)列上面沒有not null的約束，那么此時(shí)not in的寫法就無法對(duì)子查詢進(jìn)行展開，一般我們會(huì)看見形如下面的filter執(zhí)行計(jì)劃：
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 13 10:14:42 2014

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autotrace traceonly;
SQL> set linesize 140;
SQL> select * from table02 where object_id not in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52376 | 9053K| 3430 (1)| 00:00:42 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 50979 | 647K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE01" "TABLE01"
WHERE LNNVL("OBJECT_ID"
:B1)))
3 - filter(LNNVL("OBJECT_ID"
:B1))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
17188464 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

而這個(gè)執(zhí)行成本往往非常高，而如果我們添加一個(gè)not null的約束，或者改寫下sql或者添加not null約束來取消這個(gè)特別消耗成本的filter

1）改寫成minus寫法：
SQL> select * from table02 a minus
2 select * from table02 where object_id in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 1546480765

--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 52408 | 18M| | 4674 (54)| 00
:00:57 |
| 1 | MINUS | | | | | |
|
| 2 | SORT UNIQUE | | 52408 | 9058K| 21M| 2189 (1)| 00
:00:27 |
| 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| | 154 (2)| 00
:00:02 |
| 4 | SORT UNIQUE | | 52409 | 9724K| 19M| 2484 (1)| 00
:00:30 |
|* 5 | HASH JOIN | | 52409 | 9724K| | 308 (2)| 00
:00:04 |
| 6 | TABLE ACCESS FULL| TABLE01 | 53662 | 681K| | 153 (1)| 00
:00:02 |
| 7 | TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| | 154 (2)| 00
:00:02 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
2296 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

這里邏輯讀降了好多，雖然cost感覺好像比上述的filter執(zhí)行成本還要大，但是sql的相應(yīng)時(shí)間確明顯比f(wàn)ilter好太多了。

2 給子表和主表增加not null的約束:

SQL> alter table table01 modify object_id not null;
Table altered.
SQL> alter table table02 modify object_id not null;
Table altered.

SQL> select * from table02 where object_id not in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 35610947

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 190 | 308 (2)| 00:00:04 |

|* 1 | HASH JOIN RIGHT ANTI| | 1 | 190 | 308 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL | TABLE01 | 53662 | 681K| 153 (1)| 00:00:02 |

| 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
265 recursive calls
0 db block gets
1557 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

注意這里需要對(duì)子表和主表都添加not null約束，不然在10g的cbo下，oracle還是會(huì)選擇性能較差的filter。

我們看看各個(gè)版本優(yōu)化器對(duì)于in和exists處理的變化（Table01和table02的object_id上都有not null約束）
SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where exists (select 1 from table01 a where a.object_id=b.object_id);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"A"."OBJECT_ID"=:B1))
3 - filter("A"."OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17191469 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where not
exists (select 1 from table01 a where a.object_id=b.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"A"."OBJECT_ID"=:B1))
3 - filter("A"."OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17191469 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where objec
t_id in (select object_id from table01 a);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2067593584

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31M| 5705M| | 469 |
|* 1 | HASH JOIN | | 31M| 5705M| | 469 |
| 2 | VIEW | VW_NSO_1 | 56115 | 712K| | 251 |
| 3 | SORT UNIQUE | | 56115 | 712K| 2216K| 251 |
| 4 | TABLE ACCESS FULL| TABLE01 | 56115 | 712K| | 67 |
| 5 | TABLE ACCESS FULL | TABLE02 | 56115 | 9699K| | 67 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="$nso_col_1")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where objec
t_id not in (select object_id from table01 a);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"OBJECT_ID"=:B1))
3 - filter("OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

這里看出在8i的優(yōu)化器模式下，in的子查詢被展開為表連接了，其余的not in、exists、not exists的子查詢并不被選擇展開為表連接，而是采用一種filter的關(guān)聯(lián)方式，雖然這里的執(zhí)行成本初看來filter的cost更小，但是sq的相應(yīng)時(shí)間消耗資源的比例確實(shí)天壤之別，很多情況我們并不能以cost值去衡量這個(gè)sql性能。

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where exis
ts (select 1 from table01 a where a.object_id=b.object_id);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 268410134

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50075 | 5183K| | 236 |
|* 1 | HASH JOIN SEMI | | 50075 | 5183K| 5136K| 236 |
| 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 |
| 3 | VIEW | VW_SQ_1 | 50075 | 635K| | 68 |
| 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="B"."OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where not e
xists (select 1 from table01 a where a.object_id=b.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 2991049530

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 |
|* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 |
| 2 | TABLE ACCESS FULL| TABLE02 | 58373 | 9M| | 68 |
| 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where objec
t_id in (select object_id from table01 a)；

Execution Plan
----------------------------------------------------------
Plan hash value: 1361234999

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50075 | 5183K| | 236 |
|* 1 | HASH JOIN SEMI | | 50075 | 5183K| 5136K| 236 |
| 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 |
| 3 | VIEW | VW_NSO_1 | 50075 | 635K| | 68 |
| 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="$nso_col_1")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where objec
t_id not in (select object_id from table01 a);

Execution Plan
----------------------------------------------------------
Plan hash value: 2991049530

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 |
|* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 |
| 2 | TABLE ACCESS FULL| TABLE02 | 58373 | 9M| | 68 |
| 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

看出在9i優(yōu)化器環(huán)境下，in和exists都被展開為表連接，此時(shí)cbo走的hash join的連接方式。
由于主表和子表的object_id上有not null的約束，所以這里not in和not exists執(zhí)行計(jì)劃也都相同，對(duì)于關(guān)聯(lián)數(shù)據(jù)較多的sql，hash join往往比nested loop高效很多。

oracle 10g的優(yōu)化器對(duì)于in、exists、not in和not exists區(qū)別并不大，到了11g的優(yōu)化器，新增了null aware anti join算法，此時(shí)并不需要表中有not null約束，也能走h(yuǎn)ash join的連接方式。

關(guān)于in、exists、not in和not exists一直是很多朋友糾結(jié)的問題，小魚這里簡(jiǎn)單總結(jié)下：
在oracle 8I下，in是可以展開為表連接的，而not in、exists、not exists會(huì)選擇filter執(zhí)行計(jì)劃，如果被驅(qū)動(dòng)表沒有高效索引，驅(qū)動(dòng)表數(shù)據(jù)返回較多，這個(gè)執(zhí)行計(jì)劃往往存在很嚴(yán)重的性能問題
在oracle 9I到oracle 10g下，in和exists沒有多大性能的區(qū)別，而not in和not exists則可能有所區(qū)別，主要看關(guān)聯(lián)列是否有not null約束，如果沒有也只能走filter的執(zhí)行計(jì)劃，而有則會(huì)選擇hash join和filter的中優(yōu)秀的執(zhí)行方式
在oracle 11g下，由于新增了null-aware anti join的算法，in和exists基本沒有區(qū)別了，既可以走h(yuǎn)ash join也可以走filter。

從此in、exists、not in、not exists的經(jīng)典問題可能并不絕對(duì)了，雖然優(yōu)化器有諸多的缺陷，但是cbo確實(shí)在不斷的改進(jìn)自己，這個(gè)是值得慶幸的！

而現(xiàn)在我們來看看返回結(jié)果上有什么區(qū)別：
SQL> select * from t01;

ID NAME
---------- ----------
1 xiaoyu
2 xiaobai
3

SQL> select * from t02;

ID NAME
---------- ----------
10 xiaoyu
20 xiaotian

SQL> select * from t01 where t01.name in (select name from t02);

ID NAME
---------- ----------
1 xiaoyu

SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name);

ID NAME
---------- ----------
1 xiaoyu

來看看not in和not exists：
SQL> select * from t01 where t01.name not in (select name from t02);

ID NAME
---------- ----------
2 xiaobai

SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na
me);

ID NAME
---------- ----------
3
2 xiaobai

看出這里的子查詢中in和exists返回結(jié)果沒有區(qū)別，not in的只返回一行數(shù)據(jù)，而not exists確返回了兩行數(shù)據(jù)，其實(shí)我們應(yīng)該是希望返回兩行數(shù)據(jù)的，那么如果我們?cè)賢02表上面添加一個(gè)name null的rows來看看

SQL> insert into t02 values(30,null);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from t01 where name in (select name from t02);

ID NAME
---------- ----------
1 xiaoyu
SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name);

ID NAME
---------- ----------
1 xiaoyu

SQL> select * from t01 where name not in (select name from t02);

no rows selected

SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na
me);

ID NAME
---------- ----------
3
2 xiaobai

這里看出in和exists對(duì)于null處理沒有變化，但是not in和not exists就不同了，not exists對(duì)于子表的null會(huì)直接略掉，也就是認(rèn)為滿足這個(gè)not exists的條件，而not in對(duì)于子表的null是敏感的，換句話說只要子表有null值，則not in不返回任何結(jié)果集。