MySQL子查询（一）—— EXISTS与IN

原文：http://www.innomysql.net/article/69.html

EXISTS是一个非常强大的谓词，它允许数据库高效地检查指定查询是否产生某些行。通常EXISTS的输入是一个子查询，并关联到外部查询，但这并不一定是必须的。根据子查询是否返回行，该谓词返回TRUE或FALSE。例如下面的SQL语句：

1 2 3 4 5 6

SELECT customerid,companyname FROM customers  AS A WHERE country =  'Spain'       AND EXISTS              (  SELECT *  FROM orders  AS B          WHERE A.customerid = B.customerid )

通过下图的查询计划可以发现，SQL优化器首先根据WHERE条件先将country列为Spain的行数据取出，对于每个匹配的customerid，该执行计划对orders表上customerid索引进行一次查询，以检查orders表中是否有customerid的订单。子查询中的索引是非常必须的，因为这可以加速对于表orders的访问。

上述的SQL语句可以重写为IN子查询，并且两者的查询计划是相同的：

1 2 3 4

SELECT customerid,companyname FROM customers  AS A WHERE country =  'Spain'       AND customerid  IN (  SELECT customerid  FROM orders );

实际在MySQL 5.6版本之前，其对于IN语句的优化是“LAZY”的。这意味着对于IN子句，如果不是显示的列表定义，如IN （‘a’，’b‘，’c‘），那么IN子句都会被转换为EXISTS的相关子查询。如下这句独立子查询：

1	SELECT ...  FROM t1  WHERE t1.a  IN ( SELECT b  FROM t2);

优化器会将该语句重写为如下的相关子查询：

1 2	SELECT ...  FROM t1  WHERE EXISTS ( SELECT 1  FROM t2  WHERE t2.b = t1.a);

这对性能会产生巨大的影响，因为转化为相关子查询后，就会对外部产生依赖（而通过我这么多年的观察，应用开发人员非常喜欢使用子查询，因为子查询相对更容易理解）。因此在MySQL 5.6版本之前子查询的优化方法之一就是将其重写为JOIN语句从而提升性能，如下面的IN子查询：

1 2 3 4

SELECT o_custkey  FROM orders WHERE o_custkey  IN      (  SELECT c_custkey  FROM customer          WHERE c_acctbal < -500 );

可以改写为：

1 2 3

SELECT o_custkey  FROM orders,customer WHERE o_custkey = c_custkey      AND c_acctbal < -500;

此外，EXISTS与IN对三值逻辑的判断上还有一个小小的区别。对于EXISTS其总是返回TRUE或者FALSE。而对于IN，除了TRUE、FALSE值外，对于NULL值还有可能返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与EXISTS一样，SQL优化器会选择相同的执行计划。

但是对于输入列表中包含NULL时，NOT EXISTS和NOT IN之间差异就表现的非常的明显了。因为对于输入列表中包含NULL值，IN总是返回TRUE和UNKNOWN，因此NOT IN总是返回NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。来看下面的例子：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql>  SELECT NULL IN ( 'a' , 'b' ,  NULL )G; *************************** 1. row *************************** NULL IN ( 'a' , 'b' ,  NULL ):  NULL 1 row  in set (0.00 sec)   mysql>  SELECT NULL NOT IN ( 'a' , 'b' ,  NULL )G; *************************** 1. row *************************** NULL NOT IN ( 'a' , 'b' ,  NULL ):  NULL 1 row  in set (0.00 sec)   mysql>  SELECT 'a' NOT IN ( 'a' , 'b' ,  NULL )G; *************************** 1. row *************************** 'a' NOT IN ( 'a' , 'b' ,  NULL ): 0 1 row  in set (0.00 sec) mysql>  SELECT 'c' NOT IN ( 'a' , 'b' ,  NULL )G; *************************** 1. row *************************** 'c' NOT IN ( 'a' , 'b' ,  NULL ):  NULL 1 row  in set (0.00 sec)

’a’ IN和NOT IN的返回值都是显而易见的。NULL IN （‘a’,’b’,NULL）返回的是NULL，因为NULL值进行比较返回的是UNKNOWN状态。最后，对于‘c’ NOT IN（’a’，‘b’，NULL）的结果可能出乎一些人的意料之外，其返回的是NULL。之前已经说过，对于包含NULL值的NOT IN来说，其总是返回FALSE和UNKNOWN。而对于NOT EXISTS，其总是返回TRUE和FALSE。而这就是NOT EXISTS和NOT IN的最大区别。