關於Oracle中in和exists的區別

以Oracle資料庫為例

1 exists的使用

Exists用於只能用於子查詢，可以替代in，若匹配到結果，則退出內部查詢，並將條件標誌為true，傳回全部結果資料,in不管匹配到匹配不到都全部匹配完畢，使用exists可以將子查詢結果定為常量，不影響查詢效果，而且效率高。如查詢所有銷售部門員工的姓名，對比如下：

IN is often better if the results of the subquery are very small

When you write a query using the IN clause, you're telling the rule-based optimizer that you

want the inner query to drive the outer query.

When you write EXISTS in a where clause, you're telling the optimizer that you want the outer

query to be run first, using each value to fetch a value from the inner query.

In many cases, EXISTS is better because it requires you to specify a join condition, which can

invoke an INDEX scan. However, IN is often better if the results of the subquery are very

small. You usually want to run the query that returns the smaller set of results first.

In和exists對比：

若子查詢結果集比較小，優先使用in，若外層查詢比子查詢小，優先使

用exists。因為若用in，則oracle會優先查詢子查詢，然後匹配外層查詢，

若使用exists，則oracle會優先查詢外層表，然後再與內層表匹配。最佳化

匹配原則，拿最小記錄匹配大記錄。

在看下官網文檔裡怎麼說：

首先看下文法，文法很簡單，一看例子大家都會明白，但一定要注意Operation裡面的那句話很重要，因為這樣關係到null的問題，是返回至少一行就返回true值。

1 2 下一頁