MySQL 子查询（一）
源自MySQL 5.7 官方手册 13.2.10 Subquery Syntax
〇、MySQL子查询介绍
子查询指的是嵌套在某个语句中的SELECT语句。
MySQL支持标准SQL所要求的所有子查询形式和操作，此外还进行了一些扩展。
下面就是一个有子查询的示例：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
在此示例中，SELECT * FROM t1 ...是外部查询（或外部语句），而（SELECT column1 FROM t2）是子查询。子查询嵌套在外部查询中，实际上可以将子查询嵌套在其他子查询中，达到相当深的程度。子查询必须始终出现在括号内。
子查询的优点：
- 允许结构化的查询，以便可以隔离语句的每个部分；
- 可以替代复杂的连接和联合；
- 相比连接和联合，有更高的可读性。实际上，正是子查询这个创新给了人们灵感把SQL叫做结构化查询语言。
子查询的语法要点
下面是一个示例语句，显示了SQL标准指定的并在MySQL中支持的子查询语法的要点：
DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));
一个子查询可以返回一个标量（单个值），单个行，单个列或一个表（一个或多个列的一行或多行）。他们分别叫做标量子查询、列子查询、行子查询以及表子查询。
返回特定类型结果的子查询通常只能在某些上下文中使用，接下来的章节会阐述。
子查询可以应用在大部分语句中，MySQL对此限制很少。
子查询可以包含很多普通SELECT语句中的关键字：DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index hints, UNION constructs, comments, functions等。
一个子查询的外接语句可以为：SELECT, INSERT, UPDATE, DELETE, SET, or DO。
但是在MySQL中，不能在修改一个表的同时在子查询中对同一个表进行SELECT操作。这适用于DELETE，INSERT，REPLACE，UPDATE等语句，还有LOAD DATA（（因为子查询可以在SET子句中使用））。
关于优化器怎么处理子查询的知识，see Section 8.2.2，“Optimizing Subqueries, Derived Tables, and View References”。
有关子查询使用限制的讨论，包括某些形式的子查询语法的性能问题，see Section C.4, “Restrictions on Subqueries”。
一、将子查询作为标量操作数
在此查询最简单的形式中，子查询是一个返回单个值的标量子查询（a scalar subquery）。标量子查询是一个简单的操作数，您几乎可以在将它使用在任何单个列值或字面值合法的地方。你可以期望它具有一般操作数都拥有的特征：数据类型，长度，可以为NULL的指示，等等。
示例：
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);
这个查询中的子查询返回单个值——“abcde”，数据类型为CHAR，长度为5，字符集和排序规则等于CREATE TABLE时生效的默认值，以及一个关于该列值可以为NULL的提示。
如果子查询的结果为空集，那么单值子查询所取回的值的NULL性并不会直接被复制，因为此时子查询的结果就为NULL。如上面的子查询，如果t1为空表，那么子查询的结果将为NULL，即使表t1中的S2列含有NOT NULL约束。
很少有一个标量子查询不能被使用的情况。如果一个语句只允许一个字面量值，那此时你无法使用一个子查询。例如，LIMIT要求整数类型的字面值参数，LOAD DATA要求一个代表文件路径的字面量的字符串值。此时你就不能使用子查询来提供这些值。
当你在接下来章节的示例中看到相当简洁的子查询时，可以联想下在自己的代码中的子查询使用更加多样化和复杂的构造。
假设现在有两个表：
CREATE TABLE t1 (s1 INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (s1 INT); INSERT INTO t2 VALUES (2);
然后之心一个SELECT：
SELECT (SELECT s1 FROM t2) FROM t1; +---------------------+ | (SELECT s1 FROM t2) | +---------------------+ | 2 | +---------------------+ 1 row in set (0.00 sec)
结果为2，因为在表t2中有一行数据，s1列值为2。
标量子查询可以是表达式的一部分，但记得加括号，即使子查询只是作为操作数为函数提供参数。
SELECT UPPER((SELECT s1 FROM t1)) FROM t2; +----------------------------+ | UPPER((SELECT s1 FROM t1)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.01 sec)
二、使用子查询进行比较
子查询最常见的用法是：
non_subquery_operand comparison_operator (subquery)
compare_operator是以下运算符之一：
= > < >= <= <> != <=>
例如：
... WHERE 'a' = (SELECT column1 FROM t1)
MySQL也允许这种结构：
non_subquery_operand LIKE (subquery)
在曾经某个时间，子查询的唯一合法位置是在比较的右侧，您可能仍然会发现一些坚持这一点的旧DBMS。
下面是一个常见形式子查询比较的示例，您无法对连接执行此操作。它找到表t1中column1值等于表t2中最大值的所有行：
SELECT * 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);
若是为了将子查询与标量进行比较，子查询必须返回标量。
若是为了将子查询与行构造函数进行比较，子查询必须是行子查询，该子查询返回与行构造函数具有相同数量值的行。See Section 13.2.10.5, “Row Subqueries”.
三、带有ANY，IN或SOME的子查询
语法：
operand comparison_operator ANY (subquery) operand IN (subquery) operand comparison_operator SOME (subquery)
compare_operator是以下运算符之一：
= > < >= <= <> !=
ANY关键字，必须紧跟比较运算符后，意味着“如果操作数与子查询中多返回的列中的任意值的比较为TRUE，那就返回TRUE”。例如：
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
假设表t1中有一行包含（10）。如果表t2包含（21,14,7），则表达式为TRUE，因为t2中的值7小于10。
如果表t2包含（20,10），或者表t2为空，则表达式为FALSE。
如果表t2包含（NULL，NULL，NULL），则表达式结果是未知的（即NULL）。
与子查询一起使用时，单词IN是= ANY的别名。因此，这两个陈述是相同的：
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
与表达式列表一起使用时，IN和= ANY不是同义词。 IN可以采用表达式列表，但是= ANY不能。See Section 12.3.2, “Comparison Functions and Operators”.
NOT IN不是<> ANY的别名，而是<> ALL的别名。See Section 13.2.10.4, “Subqueries with ALL”.
SOME这个词是ANY的别名。因此，这两个陈述是相同的：
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
SOME这个词很少用到，但是这个例子说明了为什么它可能有用。
对于大多数人来说，英语短语“a is not equal to any b”意味着“没有b等于a”。但这不是SQL语法的含义。在SQL中，该短语意味着“有一些b与a不相等。”使用<> SOME有助于确保每个人都理解查询的真正含义。
四、带有ALL的子查询
operand comparison_operator ALL (subquery)
ALL关键字必须紧跟在比较操作符后，意思是“只有操作数与子查询返回的列中的所有值进行比较都为true，则这个比较表达式也返回true”。例如：
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
假设表t1中有一行包含（10）。如果表t2包含（-5,0，+ 5），则表达式为TRUE，因为10大于t2中的所有三个值。如果表t2包含（12,6，NULL，-100），则表达式为FALSE，因为表t2中的单个值12大于10。如果表t2包含（0，NULL，1），则表达式是未知的（即NULL）。
而如果表t2为空，则表达式为TRUE。因此，当表t2为空时，以下表达式为TRUE：
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
但是当表t2为空时，以下表达式为NULL：
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
此外，当表t2为空时，以下表达式为NULL：
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
通常，包含NULL值和空表的表是“边缘情况”。
所以在编写子查询时，请始终考虑是否考虑了这两种可能性。
NOT IN是<> ALL的别名。因此，这两个陈述是相同的：
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);
