MySQL子查询(一)—— EXISTS与IN

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

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的访问。

MySQL子查询(一)—— EXISTS与IN - insidemysql - Inside MySQL
 
 上述的SQL语句可以重写为IN子查询,并且两者的查询计划是相同的:

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的相关子查询。如下这句独立子查询:

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

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

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

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

SELECT o_custkey FROM orders

WHERE o_custkey IN

    ( SELECT c_custkey FROM customer

        WHERE c_acctbal < -500 );

可以改写为:

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。来看下面的例子:

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/

MySQL子查询(一)—— EXISTS与IN

原文:http://www.innomysql.net/article/69.html EXISTS是一个非常强大的谓词,它允许数据库高效地检查指定查询是否产生某些行。通常EXISTS的输入...

mysql 子查询in与exists互换

用mysql子查询in写sql语句逻辑比较明确,但是使用in有一定的缺陷,比如in的结果集长度是受限制的,大数据量的时候无法使用,同时在大数据量的时候,使用in,查询的性能有一定变慢。综合考虑,将原先...

SQL优化经典案例----让in/exists子查询作为驱动表

SQL优化经典案例----让in/exists子查询作为驱动表   ---http://blog.sina.com.cn/s/blog_61cd89f60102efam.html  ...

如何让in/exists 子查询(半连接)作为驱动表?

一哥们问我,怎么才能让子查询作为驱动表? SQL如下: select rowid rid from its_car_pass7 v where 1 = 1 and pass_da...

【semi join】IN/EXISTS子查询的区别及其优化方式

【semi join】IN/EXISTS子查询的区别及其优化方式 2013-04-27 12:45:18 分类: Oracle     在ORACLE 11G大行其道的今天,还有很多人受...

关于所使用的spark版本中的spark sql不支持exists和in等子查询语句的解决方案记录

stackoverflow上一篇很好的问题解答解决方法: SparkSQL doesn't currently have EXISTS & IN. "(Latest) Spark ...

如何让in/exists 子查询(半连接)作为驱动表?

怎么才能让子查询作为驱动表? SQL如下: [html] view plaincopyprint? select  rowid rid      from...
  • jumewo
  • jumewo
  • 2014年08月15日 17:57
  • 357

MySQL中子查询IN,EXISTS,ANY,ALL,SOME,UNION介绍

转自:http://www.cnblogs.com/blueoverflow/archive/2015/08/08/4712320.html 阅读目录(Content) 1.ANY关键字2.ALL关...

MySQL中exists与in的使用 以及查询效率比较

exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,...

mysql 两表join与in子查询的比较

有两个表 promotion_full_reduction base_user_favorite_item 现在要查询用户收藏的商品中参加促销了的商品个数,有两种写法,一种是使用in子查询:SELEC...
  • joenqc
  • joenqc
  • 2017年06月16日 15:40
  • 1048
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL子查询(一)—— EXISTS与IN
举报原因:
原因补充:

(最多只允许输入30个字)