1）exists 和 not exists 用法
select * from a where exists (select * from b where a.id=b.id); 找A表在B表存在的记录
select * from a where not exists (select * from b where a.id=b.id)； 找A表不存在于B表的记录

看下执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | A | | | |
|* 3 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."ID"=:B1))
3 - filter("B"."ID"=:B1)

2）minus用法
select * from a minus select * from b; 查找A表中不存在于B表的记录

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MINUS | | | | |
| 2 | SORT UNIQUE | | | | |
| 3 | TABLE ACCESS FULL | A | | | |
| 4 | SORT UNIQUE | | | | |
| 5 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------

3）in和not in的用法
select * from a where a.id not in (select * from b);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | A | | | |
|* 3 | TABLE ACCESS FULL | B | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
LNNVL("B"."ID"<>:B1)))
3 - filter(LNNVL("B"."ID"<>:B1))

三种执行计划都列出来了，根据三种执行计划的特性，可以总结出：
1）A数据量小而B数据量非常大时，exists 效率非常高
2）A数据量非常大而B数据量小时，in 效率非常高
3）minus自动去重，他适合两个都是比较小的表。