MySQL(三)：子查询-白红宇

MySQL(三)：子查询

阅读量：154 次

发布时间：2019-02-27

本文共 6294 字，大约阅读时间需要 20 分钟。

子查询

子查询是一项不实用的功能，因为其性能是很差的，使用子查询后，SQL语句的查询性能会变得非常糟糕。

子查询的优点和限制

首先，子查询是指在一个SELECT语句中嵌套另一个SELECT语句。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)

在这个例子中，SELECT * FROM t1是外部查询，后面括号的就是子查询，一般来说，子查询是嵌在外部查询中的，但也可以将两个或两个以上的子查询进行嵌套，需要注意的是，子查询是必须包含括号的。

子查询的好处

子查询允许结构化的查询，这样就可以把一个查询语句的每个部分分开。

子查询提供了另一种方式来执行有些需要复杂的JOIN和UNION来实现的操作

子查询的可读性高。

一个子查询会返回一个标量（单一值）、一个行、一个列或者一个表

子查询的限制

子查询的限制是其外部语句必须是以下语句之一：SELECT、INSERT、UPDATE、DELETE、SET或DO

另一个限制就是，目前用户不能既在一个子查询中修改一个表，又在同一个表中进行选择，即当外部语句使用了子查询时，子查询里面的字段是不可以直接使用的，要想使用就要再写多一次子查询

使用子查询进行比较

比如

‘a’ = (SELECT column1 FROM t1)

SELECT column1 FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);

//找到t1表中，哪些数据出现了两次SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

使用ANY、IN和SOME进行子查询

使用ANY

ANY关键词必须与一个比较操作符一起使用，ANY关键词的意思是“对于子查询返回的列中的任一数值，如果比较结果为TRUE，则返回TRUE”。比如

//找到t1表中的s1大于t2表中的s1列中任意一个值的数据SELECT s1 FROM t1 WHERE s1 > ANY(SELECT s1 FROM t2);

再比如

这是t5表

这是t6表

SELECT t5.`id`,t5.`s1`  FROM t5 WHERE t5.`s1` > ANY(SELECT t6.`s1`  FROM t6);

结果为

出现这种结果是因为，t5表中除了s1=1之外，其他都可以在t6表中的s1列找到比其更小的值（表达式会返回TRUE，即t5.s1 > ANY(…)返回TRUE）,但注意，如果t6中的s1列存在NULL的话，那么表达式会返回UNKNOWN。

使用IN

IN其实是"=ANY"的别名，因此，下面这两个SQL是一样的

SELECT s1 FROM t1 WHERE s1 = ANY(SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);

使用SOME

SOME的话，其实就是ANY的别名，下面两条SQL是一样的效果，所以SOME几乎没什么人用，但SOME也是很有意义的，也就是从理解上，比如s1 > ANY(…)，从英语上看，应该是s1大于任何一个，但实际上在SQL中只是大于一部分（至少一个），而使用SOME，s1 > SOME(…)，从英语上看，是s1大于一些，与SQL表达的意思更为贴近。

SELECT s1 FROM t1 WHERE s1 
   <比较符>
     ANY(SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 
    <比较符>
      SOME(SELECT s1 FROM t2);

使用ALL进行子查询

使用ALL进行子查询的语法

operand comparison_operator ALL(subquery)

词语ALL必须与比较操作符一起使用，ALL的意思，对于子查询返回的列中的所有值进行比较，如果都为TRUE，返

回TRUE。

比如还是上面的t5和t6

SELECT t5.`id`,t5.`s1`  FROM t5 WHERE t5.`s1` > ALL(SELECT t6.`s1`  FROM t6);

在这里插入图片描述可以看到什么都没有返回，因为t5中的s1最大的为5，t6中的s1最大也为5，所以，t5的s1肯定不会出现比t6的s1列中所有值大的数，也就是表达式返回的肯定为FALSE。

同理，如果出现跟NULL进行比较的话，也是会返回UNKNOWN，如果ALL(…)里面的整张表是一张空表，那么返回的最终结果是为TRUE。

//如果t2表是一张空表，那么最终比较返回的结果为TRUESELECT * FROM t1 WHERE 1 > ALL(SELECT s1 FROM t2)//当t2表是一张空表时，最终比较返回的结果为NULLSELECT * FROM t1 WHERE 1 > (SELECT S1 FROM t2)//当t2表是一张空表时，最终比较返回的结果为NULLSELECT * FROM t1 WHERE 1 > ALL(SELECT MAX(s1) FROM t2)//因为使用MAX了之后，只剩下一个值，比较的话跟下面的一致SELECT * FROM t1 WHERE 1 > (SELECT MAX(s1) FROM t2)

NOT IN是<>ALL的别名，下面的两条SQL是相同的

SELECT s1 FROM t1 WHERE s1 <> ALL(SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

独立子查询

子查询可以按两种方式进行分类，若按照期望值的数量（这里的期望值是外部查询希望的，也就是需要子查询返回的值个数），可以将子查询分为标量子查询和多值子查询（标量就是前面我们提到的子查询返回的结果只有一个，多量的话就是多个，其实就是多列或多行）；若按查询对外部查询的依赖可以分为独立子查询（self-contained subquery）和相关子查询（correlated subquery）。标量子查询和多值子查询可以是独立子查询，也可以是相关子查询。

独立查询是指不依赖外部查询而运行的子查询，与相关子查询相比，独立查询更便于SQL语句的调试。

标量子查询可以出现在查询中希望产生标量值的任何地方，而多值子查询可以出现在查询中希望产生多值集合的任何地方，只要标量子查询返回的是单个值或者NULL，就说明这个标量子查询是合理的，但如果返回的是多个值，那么数据库是会抛错的。

举个栗子

//这两条都是合理的标量子查询//外部查询希望的是一个标量，子查询也是标量子查询SELECT 'a' = (SELECT 'a') AS t;SELECT 'a' = (SELECT NULL) AS t;//而下面的子查询会抛出异常//因为外部查询希望的是一个标量，而子查询是多值子查询，所以会报错//union和Union all 其实是将两个select查询的结果集合成一个表返回SELECT 'a' = (SELECT 'a' UNION ALL SELECT 'b') AS t;

拓展一下

其实独立子查询在官方文档中是没有介绍的，这是因为很多时候，独立子查询会经过Mysql的引擎自带的优化变为相关子查询，比如下面的sql

这里先介绍一下EXISTS

EXISTS代表的意思为存在，在子查询用EXISTS时，只会返回TRUE或者FALSE

//很明显看到，下面这条SQL是独立子查询，而且是多值子查询SELECT ... FROM t1 WHERE t1.column1 IN (SELECT b FROM t2);//但其实，MySQL的优化器会将其变为下面这种形式//可以看到，子查询变为了相关子查询SELECT ... FROM t1 WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.b = t2.a);

EXISTS谓词

EXISTS

EXISTS是个非常强大的谓词，它允许数据库高效地检查指定查询是否产生某些行，通常EXISTS的输入是一个子查询，并且关联到外部查询，但这不是必须的，即不一定要关联到外部查询。根据子查询是否返回行，该谓词返回TRUE或FALSE（也就是子查询成功查询出数据，会返回TRUE，否则返回FALSE），与其他谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN的（只有TRUE和FALSE），如果子查询的过滤器为某行返回UNKNOWN，则表示该行不反悔，UNKNOWN会被EXISTS认定为FALSE。

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

上面这条SQL说明了EXISTS的用法，相关子查询就如上面所说的，就像遍历一样，把当前的A.customerid放进去匹配，如果相关子查询可以返回行出来，那就EXISTS(…)会返回TRUE，如果返回NULL，也就是查不到数据，就会返回FALSE，会影响WHERE子句的判断。

//将该语句改为IN子查询SELECT customerid,companyname FROM customers AS A WHERE country = "Spain" AND customerid IN (SELECT customerid FROM orders);

注意

尽管很多SELECT语句都不推荐使用*，因为这可能会引起一些问题，但是EXISTS子查询钟可以放心地使用，因为EXISTS只关系行是否存在，而不会去考虑各个列的值。

NOT EXISTS

EXISTS与IN的一个小区别体现在对三值逻辑的判断上，EXISTS总是返回TRUE或者FALSE，而对于IN，除了TRUE、FALSE值外，还有可能对NULL值返回UNKNOWN（NULL = NULL，会返回UNKNOWN），但是对UNKNOWN的处理方式跟FALSE一样。

但是NOT EXISTS与NOT IN就出现比较大的区别了。

当输入列表中包含NULL值时，IN总是返回UNKNOWN、TRUE

SELECT  NULL IN('a','b',NULL);SELECT "a" IN ("a","b",NULL);

在这里插入图片描述

因此NOT IN就会返回NOT UNKNOWN、FALSE，其实也是UNKNOWN和FALSE。

SELECT NULL NOT IN('a','b',NULL);SELECT "a" NOT IN("a","b",NULL);

在这里插入图片描述

IN和NOT IN的返回值都是显而易见的，对NULL值进行比较返回的是UNKNOWN状态，但出乎人意料的是下面这条SQL也会返回NULL（可能是因为只要跟NULL进行了比较，就会为UNKNOWN）

SELECT "c" NOT IN("a","b",NULL);

但EXISTS和NOT EXISTS是返回TRUE和FALSE，也就是没有UNKNOWN状态。

派生表

派生表被称为表子查询，与其他表一样会出现在FROM子句中，但是这是从子查询派生出的虚拟表中产生的，派生表的使用形式一般如下

//subquery expression产生派生表的select语句，derived_table_alias是派生表的别名。FROM (subquery expression) AS derived_table_alias

目前派生表在使用上有以下使用规则

列的名称必须是唯一的（别名不可以取相同的）

在某些情况下不支持LIMIT

//比如下面这些SQL//这在一般SQL是可以的SELECT "C" AS c,"B" AS c; //在子查询中却是不可以的SELECT * FROM (SELECT "c" AS c,"b" AS c) AS t;会报错，Duplicate column name "c"，就是列名c重复了//派生表也不支持LIMITSELECT customerid,companyname FROM customers AS A WHERE customerid IN(SELECT customerid FROM orders LIMIT 5)

在这里插入图片描述