返回相同结果，用不同SQL的执行计划

1）SQL> select deptno from scott.dept where deptno not in (select deptno from scott.emp);

Execution Plan
----------------------------------------------------------
Plan hash value: 304125360

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     3 |     9 |     5   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN   | PK_DEPT |     1 |     3 |     1   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP     |     2 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "EMP"
              WHERE LNNVL("DEPTNO"<>:B1)))
   2 - filter(LNNVL("DEPTNO"<>:B1))

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

2）SQL> select deptno from scott.dept where not exists (select 1 from scott.emp where scott.emp.deptno=scott.dept.deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 4049784039

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |         |     1 |     6 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

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

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

3）SQL> select deptno from scott.dept minus select deptno from scott.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288528972

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     4 |    54 |     6  (84)| 00:00:01 |
|   1 |  MINUS              |         |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|         |     4 |    12 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

4）SQL> select scott.dept.deptno from scott.dept,scott.emp where scott.dept.deptno=scott.emp.deptno(+) and emp.deptno is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 4049784039

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |         |     1 |     6 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

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

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

可以看出以上4个语句实现的功能是一致的，通过执行计划可以看出2）和4）的结果是一样的，可见以上两个表返回的数据都很少，才有此结果，要是数据量很大可能返回的结果是不同的，需要测试才知道那些是最优的