1.创造测试环境
SYS@ora122>create table a(id int,name varchar2());

SYS@ora122>insert into a values(,'a');

SYS@ora122>insert into a values(,'b');

SYS@ora122>insert into a values(,'c');

SYS@ora122>insert into a values(,'a');

SYS@ora122>commit;

2.简单exists 举例说明，对于单个exists ,子查询返回null,则结果为null,子查询非null,则查询显示输出结果
SYS@ora122> select * from a where exists(select id from a where name='a')

  ID NAME

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

    a

    b

    c

    a

SYS@ora122> select * from a where exists(select id from a where name='d');

no rows selected

SYS@ora122>select * from a where not exists(select id from a where name='a');

no rows selected

3.创建测试索引，观察执行计划，找到执行计划的规律
SYS@ora122>create index a_id on a(id);

SYS@ora122>create index a_name on a(name);

SYS@ora122>set autotrace on

SYS@ora122>select id from a where not exists(select id from a where name='a');

no rows selected

Execution Plan

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

Plan hash value:

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

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

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

|    | SELECT STATEMENT   |        |      |     |        ()| :: |

|*   |  FILTER            |        |       |       |            |          |

|    |   TABLE ACCESS FULL| A      |      |     |        ()| :: |

|*   |   INDEX RANGE SCAN | A_NAME |      |    |        ()| :: |

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

Predicate Information (identified by operation id):

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

    - filter( NOT EXISTS (SELECT  FROM "A" "A" WHERE "NAME"='a'))

    - access("NAME"='a')
可以得知，表A全表扫、not exists 查询结果为2行记录， 最终filter 返回null值

SQL改写

select id from a where id>2 and not exists(select id from a where name='a');
no rows selected

----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX RANGE SCAN| A_ID | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| A_NAME | 2 | 204 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - filter( NOT EXISTS (SELECT 0 FROM "A" "A" WHERE "NAME"='a'))
2 - access("ID">2)
3 - access("NAME"='a')         执行计划并未改变多少

SYS@ora122>SELECT 0 FROM "A" "A" WHERE "NAME"='a';

0
----------
0
0

SYS@ora122>select id from a where name='a';

ID
----
1
3

SQL改写

SYS@ora122>select * from a where name='a' and not exists(select id from a where id=8);

ID NAME
---- --------------------
1 a
3 a

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 230 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| A | 2 | 230 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | A_NAME | 2 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | A_ID | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

1 - filter( NOT EXISTS (SELECT 0 FROM "A" "A" WHERE "ID"=8))
3 - access("NAME"='a')
4 - access("ID"=8)

与IN不同的是，

in 相互对等匹配返回结果

exists 要么都可以，要么都失败

not exists 与exists本质相同

not in ，则是第一个查询结果与not in结果匹配进行过滤