SQL> create table emp as select * from scott.emp;


Table created.


SQL> create table dept as select * from scott.dept;


Table created.


SQL> set timing on
SQL> set lines 20000
SQL> set  autot on
SQL> select * from dept where deptno NOT IN ( select deptno from emp ) ;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |   | 4 |   172 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("DEPTNO"="DEPTNO")


Note
-----
   - dynamic sampling used for this statement (level=2)




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


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |   | 4 |   172 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("DEPTNO"="DEPTNO")


Note
-----
   - dynamic sampling used for this statement (level=2)




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


SQL> select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 474461924


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |   | 4 |   172 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


Note
-----
   - dynamic sampling used for this statement (level=2)




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


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 474461924


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |   | 4 |   172 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


Note
-----
   - dynamic sampling used for this statement (level=2)




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



SQL> select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 474461924


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("DEPTNO"="DEPTNO")
   2 - filter("DEPTNO" IS NOT NULL)
   3 - filter("DEPTNO" IS NOT NULL)


Note
-----
   - dynamic sampling used for this statement (level=2)




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


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 474461924


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT | 4 |   120 | 3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |   182 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("DEPTNO"="DEPTNO")
   2 - filter("DEPTNO" IS NOT NULL)
   3 - filter("DEPTNO" IS NOT NULL)


Note
-----
   - dynamic sampling used for this statement (level=2)




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

结论：11g与空值有关，都可以用到anti的半连接算法，执行计划一样，性能一样