以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