| In与Exists | DBA之路：OoNiceDream |

In与Exists

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/01/in-versus-exists.html

何时适合用In，何时适合用Exists，今天正好在《Performance Tuning Guide and Reference》中看到些描述，记录下：

Use of EXISTS versus IN for Subqueries
In certain circumstances, it is better to use IN rather than EXISTS.
In general, if the selective predicate is in the subquery, then use IN.
If the selective predicate is in the parent query, then use EXISTS.

适合使用IN的情况，eg：

The SQL statement using IN:
SELECT /* IN example */
 e.employee_id, e.first_name, e.last_name, e.salary
  FROM employees e
 WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 4 */
                           FROM orders o
                          WHERE o.customer_id = 144); /* Note 3 */
 
Note 3: This shows the line where the correlated subqueries 
include the highly selective predicate customer_id = number
 
Note 4: This indicates that an IN is being used. The subquery is no 
longer correlated, because the IN clause replaces the join in the subquery.

适合使用EXISTS的情况，eg：

The following SQL statement uses EXISTS:
SELECT /* EXISTS example */
 e.employee_id, e.first_name, e.last_name, e.salary
  FROM employees e
 WHERE e.department_id = 80 /* Note 5 */
   AND e.job_id = 'SA_REP' /* Note 6 */
   AND EXISTS (SELECT 1 /* Note 1 */
          FROM orders o
         WHERE e.employee_id = o.sales_rep_id); /* Note 2 */
 
Note 1: This shows the line containing EXISTS.
Note 2: This shows the line that makes the subquery a correlated subquery.
Note 5 & 6:These are the selective predicates in the parent SQL.