SELECT customerid,companyname
FROM customers AS A
WHERE country = 'Spain'
AND EXISTS
( SELECT * FROM orders AS B
WHERE A.customerid = B.customerid )
SELECT customerid,companyname
FROM customers AS A
WHERE country = 'Spain'
AND customerid IN ( SELECT customerid FROM orders );
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
优化器会将该语句重写为如下的相关子查询:
这对性能会产生巨大的影响,因为转化为相关子查询后,就会对外部产生依赖(而通过我这么多年的观察,应用开发人员非常喜欢使用子查询,因为子查询相对更容易理解)。因此在MySQL 5.6版本之前子查询的优化方法之一就是将其重写为JOIN语句从而提升性能,如下面的IN子查询:SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
可以改写为:SELECT o_custkey FROM orders
WHERE o_custkey IN
( SELECT c_custkey FROM customer
WHERE c_acctbal < -500 );
此外,EXISTS与IN对三值逻辑的判断上还有一个小小的区别。对于EXISTS其总是返回TRUE或者FALSE。而对于IN,除了TRUE、FALSE值外,对于NULL值还有可能返回UNKNOWN。 但是在过滤器中,UNKNOWN的处理方式与FALSE相同,因此使用IN与EXISTS一样,SQL优化器会选择相同的执行计划。SELECT o_custkey FROM orders,customer
WHERE o_custkey = c_custkey
AND c_acctbal < -500;
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的最大区别。
from:http://insidemysql.blog.163.com/blog/static/20283404220137121640390/