MySQL8 中文参考（六十）
15.2.11 带括号的查询表达式
译文：
dev.mysql.com/doc/refman/8.0/en/parenthesized-query-expressions.html
*parenthesized_query_expression*:
( *query_expression* [*order_by_clause*] [*limit_clause*] )
[*order_by_clause*]
[*limit_clause*]
[*into_clause*]
*query_expression*:
*query_block* [*set_op* *query_block* [*set_op* *query_block* ...]]
[*order_by_clause*]
[*limit_clause*]
[*into_clause*]
*query_block*:
SELECT ... | TABLE | VALUES
*order_by_clause*:
ORDER BY as for SELECT
*limit_clause*:
LIMIT as for SELECT
*into_clause*:
INTO as for SELECT
*set_op*:
UNION | INTERSECT | EXCEPT
MySQL 8.0.22 及更高版本支持根据前述语法的带括号的查询表达式。在其最简单形式下，带括号的查询表达式包含一个返回结果集的单个SELECT或其他语句，没有后续的可选子句：
(SELECT 1);
(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql');
TABLE t;
VALUES ROW(2, 3, 4), ROW(1, -2, 3);
(支持从 MySQL 8.0.19 开始提供的TABLE和VALUES语句。)
一个带括号的查询表达式也可以包含通过一个或多个集合操作连接的查询，例如UNION，并以任意或所有可选子句结束：
mysql> (SELECT 1 AS result UNION SELECT 2);
+--------+
| result |
+--------+
| 1 |
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
+--------+
| result |
+--------+
| 1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
ORDER BY result DESC LIMIT 1;
+--------+
| result |
+--------+
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
ORDER BY result DESC LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
| 1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 3 UNION SELECT 2)
ORDER BY result LIMIT 1 OFFSET 1 INTO @var;
mysql> SELECT @var;
+------+
| @var |
+------+
| 2 |
+------+
除了UNION之外，INTERSECT和EXCEPT集合操作符从 MySQL 8.0.31 开始提供。INTERSECT在UNION和EXCEPT之前起作用，因此以下两个语句是等效的：
SELECT a FROM t1 EXCEPT SELECT b FROM t2 INTERSECT SELECT c FROM t3;
SELECT a FROM t1 EXCEPT (SELECT b FROM t2 INTERSECT SELECT c FROM t3);
带括号的查询表达式也用作查询表达式，因此查询表达式通常由查询块组成，也可以由带括号的查询表达式组成：
(TABLE t1 ORDER BY a) UNION (TABLE t2 ORDER BY b) ORDER BY z;
查询块可以有尾随的ORDER BY和LIMIT子句，在外部集合操作、ORDER BY和LIMIT之前应用。
不能有带有尾随ORDER BY或LIMIT的查询块，而不将其包装在括号中，但可以以各种方式使用括号进行强制执行：
-
要在每个查询块上强制执行
LIMIT：(SELECT 1 LIMIT 1) UNION (VALUES ROW(2) LIMIT 1); (VALUES ROW(1), ROW(2) LIMIT 2) EXCEPT (SELECT 2 LIMIT 1); -
要在查询块和整个查询表达式上强制执行
LIMIT：(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1; -
要在整个查询表达式上强制执行
LIMIT（不带括号）：VALUES ROW(1), ROW(2) INTERSECT VALUES ROW(2), ROW(1) LIMIT 1; -
混合强制：在第一个查询块和整个查询表达式上强制执行
LIMIT：(SELECT 1 LIMIT 1) UNION SELECT 2 LIMIT 1;
本节中描述的语法受到一定的限制：
-
如果括号内有另一个
INTO子句，则不允许为查询表达式添加尾随的INTO子句。 -
在 MySQL 8.0.31 之前，当
ORDER BY或LIMIT出现在带括号的查询表达式中并且也应用于外部查询时，结果是未定义的。在 MySQL 8.0.31 及更高版本中，这将按照 SQL 标准处理。在 MySQL 8.0.31 之前，带括号的查询表达式不允许多层次的
ORDER BY或LIMIT操作，并且包含这些操作的语句将被拒绝，并显示ER_NOT_SUPPORTED_YET。在 MySQL 8.0.31 及更高版本中，此限制已被取消，并允许嵌套的带括号的查询表达式。支持的最大嵌套级别为 63；这是在解析器执行任何简化或合并之后。这里展示了这种语句的一个示例：
mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3; +---+ | a | +---+ | a | | b | +---+ 2 rows in set (0.00 sec)你应该注意，在 MySQL 8.0.31 及更高版本中，当折叠括号表达式体时，MySQL 遵循 SQL 标准语义，因此更高的外部限制不能覆盖更低的内部限制。例如，
(SELECT ... LIMIT 5) LIMIT 10最多只能返回五行。
15.2.12 REPLACE Statement
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] *tbl_name*
[PARTITION (*partition_name* [, *partition_name*] ...)]
[(*col_name* [, *col_name*] ...)]
{ {VALUES | VALUE} (*value_list*) [, (*value_list*)] ...
|
VALUES *row_constructor_list*
}
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] *tbl_name*
[PARTITION (*partition_name* [, *partition_name*] ...)]
SET *assignment_list*
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] *tbl_name*
[PARTITION (*partition_name* [, *partition_name*] ...)]
[(*col_name* [, *col_name*] ...)]
{SELECT ... | TABLE *table_name*}
*value*:
{*expr* | DEFAULT}
*value_list*:
*value* [, *value*] ...
*row_constructor_list*:
ROW(*value_list*)[, ROW(*value_list*)][, ...]
*assignment*:
*col_name* = *value*
*assignment_list*:
*assignment* [, *assignment*] ...
REPLACE的工作方式与INSERT完全相同，唯一的区别是，如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前会删除旧行。请参见第 15.2.7 节，“INSERT Statement”。
REPLACE是 MySQL 对 SQL 标准的扩展。它要么插入，要么删除并插入。对于另一个 MySQL 对标准 SQL 的扩展——要么插入，要么更新，请参见第 15.2.7.2 节，“INSERT ... ON DUPLICATE KEY UPDATE Statement”。
DELAYED插入和替换在 MySQL 5.6 中已弃用。在 MySQL 8.0 中，不再支持DELAYED。服务器会识别但忽略DELAYED关键字，将替换处理为非延迟替换，并生成一个ER_WARN_LEGACY_SYNTAX_CONVERTED警告：REPLACE DELAYED 不再受支持。该语句已转换为 REPLACE。DELAYED关键字计划在将来的版本中移除。
注意
REPLACE仅在表具有PRIMARY KEY或UNIQUE索引时才有意义。否则，它将等同于INSERT，因为没有索引可用于确定新行是否重复。
所有列的值都取自REPLACE语句中指定的值。任何缺失的列都将设置为它们的默认值，就像INSERT一样。您不能引用当前行的值并在新行中使用它们。如果您使用类似SET *col_name* = *col_name* + 1的赋值，右侧的列名引用将被视为DEFAULT(*col_name*)，因此该赋值等效于SET *col_name* = DEFAULT(*col_name*) + 1。
在 MySQL 8.0.19 及更高版本中，您可以使用VALUES ROW()指定REPLACE尝试插入的列值。
要使用REPLACE，您必须对表具有INSERT和DELETE权限。
如果一个生成的列被显式替换，唯一允许的值是DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。
REPLACE支持使用PARTITION子句显式选择分区，后面跟着逗号分隔的分区、子分区或两者名称列表。与INSERT一样，如果无法将新行插入这些分区或子分区中的任何一个，REPLACE语句将失败，并显示错误信息“Found a row not matching the given partition set”。有关更多信息和示例，请参见第 26.5 节，“分区选择”。
REPLACE语句返回一个计数，指示受影响的行数。这是删除和插入的行数之和。如果对于单行REPLACE，计数为 1，则插入了一行且未删除任何行。如果计数大于 1，则在插入新行之前删除了一个或多个旧行。如果表包含多个唯一索引，并且新行在不同唯一索引中重复值以替换一个以上的旧行是可能的。
受影响的行数计数使得很容易确定REPLACE是否仅添加了一行还是还替换了任何行：检查计数是否为 1（添加）或大于 1（替换）。
如果使用 C API，则可以使用mysql_affected_rows()函数获取受影响的行数计数。
不能在子查询中将新行替换到表中并从同一表中进行选择。
MySQL 使用以下算法进行REPLACE（以及LOAD DATA ... REPLACE）：
-
尝试将新行插入表中
-
当由于主键或唯一索引发生重复键错误而插入失败时：
-
从表中删除具有重复键值的冲突行
-
再次尝试将新行插入表中
-
在重复键错误的情况下，存储引擎可能将REPLACE作为更新而不是删除加插入来执行，但语义是相同的。除了存储引擎如何递增Handler_*xxx*状态变量可能有所不同外，没有其他用户可见的影响。
因为REPLACE ... SELECT语句的结果取决于从SELECT中的行的排序，而这种顺序并不能始终保证，所以在记录这些语句时，源和副本可能会发生分歧。因此，REPLACE ... SELECT语句被标记为不安全的基于语句的复制。当使用基于语句的模式时，这些语句在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。另请参阅 Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”。
MySQL 8.0.19 及更高版本支持TABLE以及带有REPLACE的SELECT，就像它对INSERT一样。有关更多信息和示例，请参见 Section 15.2.7.1, “INSERT ... SELECT Statement”。
当修改一个现有的非分区表以适应分区，或者修改已经分区表的分区时，您可能考虑修改表的主键（参见 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”）。您应该意识到，如果这样做，REPLACE语句的结果可能会受到影响，就像您修改非分区表的主键时一样。考虑以下由CREATE TABLE语句创建的表：
CREATE TABLE test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64) DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
当我们创建这个表并在 mysql 客户端中运行所示的语句时，结果如下：
mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)
mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)
mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts |
+----+------+---------------------+
| 1 | New | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)
现在我们创建一个几乎与第一个表相同的第二个表，唯一不同的是主键现在涵盖了 2 列，如下所示（加粗文本）：
CREATE TABLE test2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
data VARCHAR(64) DEFAULT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
*PRIMARY KEY (id, ts)* );
当我们在test2上运行与原始test表相同的两个REPLACE语句时，我们得到不同的结果：
mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)
mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts |
+----+------+---------------------+
| 1 | Old | 2014-08-20 18:47:00 |
| 1 | New | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)
这是因为在test2上运行时，id和ts列的值必须与现有行的值匹配才能替换行；否则，将插入一行。
15.2.13 SELECT Statement
15.2.13.1 SELECT ... INTO Statement
15.2.13.2 JOIN Clause
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
*select_expr* [, *select_expr*] ...
[*into_option*]
[FROM *table_references*
[PARTITION *partition_list*]]
[WHERE *where_condition*]
[GROUP BY {*col_name* | *expr* | *position*}, ... [WITH ROLLUP]]
[HAVING *where_condition*]
[WINDOW *window_name* AS (*window_spec*)
[, *window_name* AS (*window_spec*)] ...]
[ORDER BY {*col_name* | *expr* | *position*}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[*offset*,] *row_count* | *row_count* OFFSET *offset*}]
[*into_option*]
[FOR {UPDATE | SHARE}
[OF *tbl_name* [, *tbl_name*] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[*into_option*]
*into_option*: {
INTO OUTFILE '*file_name*'
[CHARACTER SET *charset_name*]
*export_options*
| INTO DUMPFILE '*file_name*'
| INTO *var_name* [, *var_name*] ...
}
SELECT 用于检索从一个或多个表中选择的行，并且可以包括 UNION 操作和子查询。从 MySQL 8.0.31 开始，还支持 INTERSECT 和 EXCEPT 操作。UNION、INTERSECT 和 EXCEPT 运算符将在本节后面更详细地描述。另请参阅 Section 15.2.15, “Subqueries”。
SELECT 语句可以以 WITH") 子句开头，以定义在 SELECT 中可访问的常用表达式。请参阅 Section 15.2.20, “WITH (Common Table Expressions)”")。
SELECT 语句最常用的子句包括：
-
每个
select_expr表示要检索的列。必须至少有一个select_expr。 -
table_references指示要检索行的表或表。其语法在 Section 15.2.13.2, “JOIN Clause” 中描述。 -
SELECT支持使用PARTITION子句显式选择分区，后跟表名中的分区或子分区列表（或两者都有）在table_reference中（参见 Section 15.2.13.2, “JOIN Clause”）。在这种情况下，仅从列出的分区中选择行，忽略表的任何其他分区。有关更多信息和示例，请参阅 Section 26.5, “Partition Selection”。 -
如果给出
WHERE子句，则指示行必须满足的条件或条件。where_condition是一个表达式，对于要选择的每一行都会计算为 true。如果没有WHERE子句，则该语句选择所有行。在
WHERE表达式中，您可以使用 MySQL 支持的任何函数和运算符，但不能使用聚合（组）函数。请参阅 Section 11.5, “Expressions”，以及 Chapter 14, Functions and Operators。
SELECT 也可用于检索计算而不参考任何表的行。
例如：
mysql> SELECT 1 + 1;
-> 2
在没有引用任何表的情况下，您可以指定 DUAL 作为虚拟表名：
mysql> SELECT 1 + 1 FROM DUAL;
-> 2
DUAL纯粹是为那些要求所有SELECT语句应具有FROM和可能其他子句的人方便而设计的。MySQL 可能会忽略这些子句。如果没有引用表，则 MySQL 不需要FROM DUAL。
通常，必须按照语法描述中显示的顺序给出使用的子句。例如，HAVING子句必须在任何GROUP BY子句之后和任何ORDER BY子句之前。如果存在INTO子句，则可以出现在语法描述指示的任何位置，但在给定语句中只能出现一次，而不是在多个位置。有关INTO的更多信息，请参见第 15.2.13.1 节，“SELECT ... INTO 语句”。
*select_expr*项列表包括指示要检索哪些列的选择列表。项指定列或表达式，或可以使用*-简写：
-
仅由单个未限定的
*组成的选择列表可以用作从所有表中选择所有列的简写：SELECT * FROM t1 INNER JOIN t2 ... -
*tbl_name*.*可以用作从命名表中选择所有列的限定简写：SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ... -
如果表具有不可见列，则
*和*tbl_name*.*不包括它们。要包括不可见列，必须明确引用它们。 -
在选择列表中与其他项目一起使用未限定的
*可能会产生解析错误。例如：SELECT id, * FROM t1为避免此问题，请使用限定的
*tbl_name*.*引用：SELECT id, t1.* FROM t1在选择列表中为每个表使用限定的
*tbl_name*.*引用：SELECT AVG(score), t1.* FROM t1 ...
以下列表提供了有关其他SELECT子句的其他信息：
-
可以使用
AS *alias_name*为*select_expr*指定别名。别名用作表达式的列名，并且可以在GROUP BY、ORDER BY或HAVING子句中使用。例如：SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;在使用标识符为*
select_expr*指定别名时，AS关键字是可选的。前面的示例可以这样写：SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name;但是，由于
AS是可选的，如果忘记在两个*select_expr*表达式之间加逗号，可能会出现一个微妙的问题：MySQL 将第二个解释为别名。例如，在以下语句中，columnb被视为别名：SELECT columna columnb FROM mytable;因此，养成在指定列别名时明确使用
AS的习惯是一个好习惯。在
WHERE子句中不允许引用列别名，因为在执行WHERE子句时可能尚未确定列值。请参见第 B.3.4.4 节，“列别名问题”。 -
FROM *table_references*子句表示要检索行的表或表。如果命名多个表，则正在执行连接。有关连接语法的信息，请参见第 15.2.13.2 节，“JOIN 子句”。对于每个指定的表，您可以选择指定别名。*tbl_name* [[AS] *alias*] [*index_hint*]使用索引提示可以为优化器提供有关在查询处理期间如何选择索引的信息。有关指定这些提示的语法的描述，请参见第 10.9.4 节，“索引提示”。
你可以使用
SET max_seeks_for_key=*value*作为一种替代方法，强制 MySQL 优先选择键扫描而不是表扫描。参见第 7.1.8 节，“服务器系统变量”。 -
你可以将默认数据库中的表称为*
tbl_name，或者作为db_name.tbl_name来明确指定数据库。你可以将列称为col_name，tbl_name.col_name，或db_name.tbl_name.col_name。除非引用会产生歧义，否则不需要为列引用指定tbl_name或db_name.tbl_name*前缀。参见第 11.2.2 节，“标识符限定符”，了解需要更明确的列引用形式的歧义示例。 -
可以使用
*tbl_name* AS *alias_name*或*tbl_name alias_name*对表引用进行别名。以下语句是等效的：SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name; -
选择输出的列可以在
ORDER BY和GROUP BY子句中使用列名、列别名或列位置进行引用。列位置是整数，从 1 开始：SELECT college, region, seed FROM tournament ORDER BY region, seed; SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s; SELECT college, region, seed FROM tournament ORDER BY 2, 3;要按照倒序排序，将
DESC（降序）关键字添加到ORDER BY子句中你要排序的列的名称中。默认是升序；可以使用ASC关键字明确指定。如果
ORDER BY出现在带括号的查询表达式中，并且也应用在外部查询中，结果是未定义的，并且可能在 MySQL 的将来版本中发生变化。使用列位置已被弃用，因为该语法已从 SQL 标准中删除。
-
在 MySQL 8.0.13 之前，MySQL 支持了一个非标准语法扩展，允许为
GROUP BY列使用显式的ASC或DESC标识符。MySQL 8.0.12 及更高版本支持带有分组函数的ORDER BY，因此不再需要使用此扩展。这也意味着在使用GROUP BY时可以对任意列进行排序，就像这样：SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;截至 MySQL 8.0.13，不再支持
GROUP BY扩展：不允许为GROUP BY列使用ASC或DESC标识符。 -
当你使用
ORDER BY或GROUP BY对SELECT中的列进行排序时，服务器仅使用由max_sort_length系统变量指示的初始字节数对值进行排序。 -
MySQL 扩展了对
GROUP BY的使用，允许选择未在GROUP BY子句中提及的字段。如果您的查询未获得预期结果，请阅读 Section 14.19，“Aggregate Functions”中关于GROUP BY的描述。 -
GROUP BY允许使用WITH ROLLUP修饰符。请参见 Section 14.19.2，“GROUP BY Modifiers”。以前，在具有
WITH ROLLUP修饰符的查询中不允许使用ORDER BY。从 MySQL 8.0.12 开始取消了此限制。请参见 Section 14.19.2，“GROUP BY Modifiers”。 -
HAVING子句与WHERE子句一样，指定选择条件。WHERE子句指定选择列表中的列的条件，但不能引用聚合函数。HAVING子句指定对由GROUP BY子句形成的组的条件。查询结果仅包括满足HAVING条件的组。（如果没有GROUP BY存在，则所有行隐式形成单个聚合组。）HAVING子句几乎是在最后应用的，就在项目发送到客户端之前，没有优化。（LIMIT在HAVING之后应用。）SQL 标准要求
HAVING只能引用GROUP BY子句中的列或聚合函数中使用的列。然而，MySQL 支持对此行为的扩展，并允许HAVING引用SELECT列表中的列以及外部子查询中的列。如果
HAVING子句引用的列存在歧义，将发出警告。在以下语句中，col2存在歧义，因为它既用作别名又用作列名：SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;标准 SQL 行为优先，因此如果
HAVING列名既在GROUP BY中使用，又作为选择列列表中的别名列，则优先使用GROUP BY列。 -
不要将应该在
WHERE子句中的项目放在HAVING中。例如，不要写如下内容：SELECT *col_name* FROM *tbl_name* HAVING *col_name* > 0;改为写成：
SELECT *col_name* FROM *tbl_name* WHERE *col_name* > 0; -
HAVING子句可以引用聚合函数，而WHERE子句不能：SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;（在某些较旧版本的 MySQL 中不起作用。）
-
MySQL 允许重复列名。也就是说，可以有多个具有相同名称的*
select_expr。这是对标准 SQL 的扩展。因为 MySQL 还允许GROUP BY和HAVING引用select_expr*值，这可能导致歧义：SELECT 12 AS a, a FROM t GROUP BY a;在该语句中，两列都具有名称
a。为确保正确使用列进行分组，请为每个*select_expr*使用不同的名称。 -
如果存在
WINDOW子句，则定义了可以被窗口函数引用的命名窗口。详情请参见 Section 14.20.4，“Named Windows”。 -
MySQL 通过在
ORDER BY子句中搜索*select_expr值，然后在FROM子句中的表列中搜索来解析未限定的列或别名引用。对于GROUP BY或HAVING子句，它会先在FROM子句中搜索，然后再在select_expr*值中搜索。（对于GROUP BY和HAVING，这与 MySQL 5.0 之前的行为不同，该行为使用与ORDER BY相同的规则。） -
LIMIT子句可用于限制SELECT语句返回的行数。LIMIT接受一个或两个数字参数，这两个参数必须都是非负整数常量，但有以下例外：-
在准备好的语句中，可以使用
?占位符标记指定LIMIT参数。 -
在存储程序中，可以使用整数值例程参数或本地变量指定
LIMIT参数。
使用两个参数时，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为 0（而不是 1）：
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15要检索从某个偏移量到结果集末尾的所有行，可以使用一个很大的数字作为第二个参数。以下语句检索从第 96 行到最后的所有行：
SELECT * FROM tbl LIMIT 95,18446744073709551615;使用一个参数时，该值指定从结果集开头返回的行数：
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows换句话说，
LIMIT *row_count*等同于LIMIT 0, *row_count*。对于准备好的语句，可以使用占位符。以下语句从
tbl表中返回一行：SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a;以下语句从
tbl表中返回第二到第六行：SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows;为了与 PostgreSQL 兼容，MySQL 还支持
LIMIT *row_count* OFFSET *offset*语法。如果
LIMIT出现在括号查询表达式中，并且也应用于外部查询，则结果是未定义的，并且可能在 MySQL 的将来版本中更改。 -
-
SELECT ... INTO形式的SELECT允许将查询结果写入文件或存储在变量中。更多信息，请参见 Section 15.2.13.1, “SELECT ... INTO Statement”。 -
如果在使用页面或行锁的存储引擎中使用
FOR UPDATE，则查询检查的行将被写锁定，直到当前事务结束。你不能在
CREATE TABLE *new_table* SELECT ... FROM *old_table* ...等语句中将FOR UPDATE作为SELECT的一部分。（如果尝试这样做，将会收到错误消息“在创建'new_table'时无法更新表'old_table'。”）FOR SHARE和LOCK IN SHARE MODE设置共享锁，允许其他事务读取检查的行，但不允许更新或删除它们。FOR SHARE和LOCK IN SHARE MODE是等效的。但是，FOR SHARE，像FOR UPDATE一样，支持NOWAIT，SKIP LOCKED和OF *tbl_name*选项。FOR SHARE是LOCK IN SHARE MODE的替代，但LOCK IN SHARE MODE仍可用于向后兼容。NOWAIT会导致FOR UPDATE或FOR SHARE查询立即执行，如果由于另一个事务持有的锁而无法获得行锁，则返回错误。SKIP LOCKED会导致FOR UPDATE或FOR SHARE查询立即执行，从结果集中排除被另一个事务锁定的行。NOWAIT和SKIP LOCKED选项对基于语句的复制不安全。注意
跳过被锁定行的查询会返回数据的不一致视图。因此，
SKIP LOCKED不适用于一般的事务工作。但是，当多个会话访问相同的类似队列的表时，可以使用它来避免锁争用。OF *tbl_name*适用于对指定表执行FOR UPDATE和FOR SHARE查询。例如：SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;当省略
OF *tbl_name*时，查询块引用的所有表都会被锁定。因此，在不与另一个锁定子句结合使用OF *tbl_name*的情况下使用锁定子句会返回错误。在多个锁定子句中指定相同的表会返回错误。如果在SELECT语句中指定了别名作为表名，则锁定子句只能使用该别名。如果SELECT语句没有明确指定别名，则锁定子句只能指定实际表名。有关
FOR UPDATE和FOR SHARE的更多信息，请参见 Section 17.7.2.4, “Locking Reads”。有关NOWAIT和SKIP LOCKED选项的更多信息，请参见 Locking Read Concurrency with NOWAIT and SKIP LOCKED。
在 SELECT 关键字之后，您可以使用许多修饰符来影响语句的操作。 HIGH_PRIORITY，STRAIGHT_JOIN 和以 SQL_ 开头的修饰符是 MySQL 对标准 SQL 的扩展。
-
ALL和DISTINCT修饰符指定是否应返回重复行。ALL（默认）指定应返回所有匹配行，包括重复行。DISTINCT指定从结果集中删除重复行。指定两个修饰符是错误的。DISTINCTROW是DISTINCT的同义词。在 MySQL 8.0.12 及更高版本中，
DISTINCT可以与使用WITH ROLLUP的查询一起使用。 (Bug #87450, Bug #26640100) -
HIGH_PRIORITY给予SELECT比更新表的语句更高的优先级。你应该只对非常快速且必须一次完成的查询使用这个选项。在表被锁定以供读取时发出的SELECT HIGH_PRIORITY查询即使有一个更新语句在等待表空闲也会运行。这只影响只使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE）。HIGH_PRIORITY不能与SELECT语句一起使用，这些语句是UNION的一部分。 -
STRAIGHT_JOIN强制优化器按照FROM子句中列出的顺序连接表。如果优化器以非最佳顺序连接表，可以使用这个选项加快查询速度。STRAIGHT_JOIN也可以在table_references列表中使用。参见第 15.2.13.2 节，“JOIN 子句”。STRAIGHT_JOIN不适用于优化器将其视为const或system表的任何表。这样的表产生一行，是在查询执行的优化阶段读取的，并且在查询执行继续之前，其列的引用被替换为适当的列值。这些表在EXPLAIN显示的查询计划中首先出现。参见第 10.8.1 节，“使用 EXPLAIN 优化查询”。这个例外可能不适用于在外连接的NULL补充侧使用的const或system表（即LEFT JOIN的右侧表或RIGHT JOIN的左侧表）。 -
SQL_BIG_RESULT或SQL_SMALL_RESULT可以与GROUP BY或DISTINCT一起使用，告诉优化器结果集有很多行或很小，分别。对于SQL_BIG_RESULT，如果创建了磁盘临时表，MySQL 直接使用它们，并倾向于对GROUP BY元素使用排序而不是使用带有键的临时表。对于SQL_SMALL_RESULT，MySQL 使用内存临时表来存储结果表，而不是使用排序。这通常不需要。 -
SQL_BUFFER_RESULT强制结果放入临时表中。这有助于 MySQL 提前释放表锁，并在向客户端发送结果集需要很长时间的情况下提供帮助。这个修饰符只能用于顶层SELECT语句，不能用于子查询或后续的UNION。 -
SQL_CALC_FOUND_ROWS告诉 MySQL 计算结果集中会有多少行，忽略任何LIMIT子句。然后可以使用SELECT FOUND_ROWS()检索行数。参见第 14.15 节，“信息函数”。注意
SQL_CALC_FOUND_ROWS查询修饰符和配套的FOUND_ROWS()函数在 MySQL 8.0.17 中已被弃用；预计它们将在未来的 MySQL 版本中被移除。请查看FOUND_ROWS()的描述以获取有关替代策略的信息。 -
在 MySQL 8.0 之前，
SQL_CACHE和SQL_NO_CACHE修饰符与查询缓存一起使用。查询缓存在 MySQL 8.0 中被移除。SQL_CACHE修饰符也被移除。SQL_NO_CACHE已被弃用，并且没有效果；预计它将在未来的 MySQL 版本中被移除。
15.2.13.1 SELECT ... INTO Statement
SELECT ... INTO形式的SELECT允许将查询结果存储在变量中或写入文件：
-
SELECT ... INTO *var_list*选择列值并将其存储到变量中。 -
SELECT ... INTO OUTFILE将所选行写入文件。可以指定列和行终止符以生成特定的输出格式。 -
SELECT ... INTO DUMPFILE将一行数据写入文件，不进行任何格式化。
给定的SELECT语句最多可以包含一个INTO子句，尽管如SELECT语法描述所示（参见第 15.2.13 节，“SELECT Statement”），INTO可以出现在不同的位置：
-
在
FROM之前。示例：SELECT * INTO @myvar FROM t1; -
在尾随锁定子句之前。示例：
SELECT * FROM t1 INTO @myvar FOR UPDATE; -
在
SELECT的末尾。示例：SELECT * FROM t1 FOR UPDATE INTO @myvar;
在 MySQL 8.0.20 中支持语句末尾的INTO位置，并且是首选位置。在 MySQL 8.0.20 中，位于锁定子句之前的位置已被弃用；预计在未来的 MySQL 版本中将删除对其的支持。换句话说，INTO在FROM之后但不在SELECT的末尾会产生警告。
不应在嵌套的SELECT中使用INTO子句，因为这样的SELECT必须将其结果返回给外部上下文。在UNION语句中对INTO的使用也受到约束；请参见第 15.2.18 节，“UNION Clause”。
对于INTO *var_list*变体：
-
*
var_list*命名一个或多个变量的列表，每个变量可以是用户定义的变量、存储过程或函数参数，或存储程序本地变量。（在准备的SELECT ... INTO *var_list*语句中，只允许使用用户定义的变量；请参见第 15.6.4.2 节，“本地变量范围和解析”。） -
所选值被分配给变量。变量的数量必须与列的数量匹配。查询应返回一行数据。如果查询未返回任何行，则会出现带有错误代码 1329 的警告（
No data），并且变量值保持不变。如果查询返回多行数据，则会出现错误 1172（Result consisted of more than one row）。如果可能语句可能检索多行数据，可以使用LIMIT 1将结果集限制为一行。SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
INTO *var_list*也可以与TABLE语句一起使用，但受到以下限制：
-
变量的数量必须与表中的列数相匹配。
-
如果表包含多行，则必须使用
LIMIT 1将结果集限制为单行。LIMIT 1必须在INTO关键字之前。
这里显示了这种语句的一个示例：
TABLE employees ORDER BY lname DESC LIMIT 1
INTO @id, @fname, @lname, @hired, @separated, @job_code, @store_id;
您还可以从生成单行的VALUES语句中选择值到一组用户变量中。在这种情况下，您必须使用表别名，并且必须将值列表中的每个值分配给一个变量。这里显示的两个语句中的每一个都等同于SET @x=2, @y=4, @z=8：
SELECT * FROM (VALUES ROW(2,4,8)) AS t INTO @x,@y,@z;
SELECT * FROM (VALUES ROW(2,4,8)) AS t(a,b,c) INTO @x,@y,@z;
用户变量名称不区分大小写。请参阅第 11.4 节，“用户定义变量”。
SELECT ... INTO OUTFILE '*file_name*'形式的SELECT将所选行写入文件。文件在服务器主机上创建，因此您必须具有FILE权限才能使用此语法。*file_name*不能是现有文件，这样可以防止修改文件，例如/etc/passwd和数据库表。character_set_filesystem系统变量控制文件名的解释。
SELECT ... INTO OUTFILE语句旨在使在服务器主机上将表转储到文本文件成为可能。要在其他主机上创建结果文件，通常使用SELECT ... INTO OUTFILE是不合适的，因为无法相对于服务器主机文件系统写入文件路径，除非可以使用服务器主机文件系统上的网络映射路径访问远程主机上文件的位置。
或者，如果远程主机上安装了 MySQL 客户端软件，您可以使用客户端命令，例如mysql -e "SELECT ..." > *file_name*在该主机上生成文件。
SELECT ... INTO OUTFILE是LOAD DATA的补充。列值被写入并转换为CHARACTER SET子句中指定的字符集。如果没有这样的子句，值将使用binary字符集进行转储。实际上，没有字符集转换。如果结果集包含几种字符集的列，则输出数据文件也是如此，可能无法正确重新加载文件。
语句中 export_options 部分的语法由与 LOAD DATA 语句一起使用的相同 FIELDS 和 LINES 子句组成。有关 FIELDS 和 LINES 子句的信息，包括它们的默认值和允许的值，请参阅 Section 15.2.9, “LOAD DATA Statement”。
FIELDS ESCAPED BY 控制如何写入特殊字符。如果 FIELDS ESCAPED BY 字符不为空，则在必要时用作前缀，避免输出时后续字符的歧义：
-
FIELDS ESCAPED BY字符 -
FIELDS [OPTIONALLY] ENCLOSED BY字符 -
FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符 -
ASCII
NUL（零值字节；实际写入转义字符后的内容是 ASCII0，而不是零值字节）
FIELDS TERMINATED BY、ENCLOSED BY、ESCAPED BY 或 LINES TERMINATED BY 字符 必须 转义，以便您可以可靠地读取文件。ASCII NUL 被转义，以便在某些分页器中更容易查看。
生成的文件不需要符合 SQL 语法，因此不需要转义其他内容。
如果 FIELDS ESCAPED BY 字符为空，则不会转义任何字符，NULL 输出为 NULL，而不是 \N。如果你的数据中的字段值包含刚才列出的字符之一，可能不是一个好主意指定一个空的转义字符。
当您想要将表的所有列转储到文本文件时，也可以使用 INTO OUTFILE 与 TABLE 语句。在这种情况下，可以使用 ORDER BY 和 LIMIT 控制排序和行数；这些子句必须在 INTO OUTFILE 之前。TABLE ... INTO OUTFILE 支持与 SELECT ... INTO OUTFILE 相同的 export_options，并且受到写入文件系统的相同限制。这里展示了这种语句的一个示例：
TABLE employees ORDER BY lname LIMIT 1000
INTO OUTFILE '/tmp/employee_data_1.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
LINES TERMINATED BY '\n';
你也可以使用 SELECT ... INTO OUTFILE 与 VALUES 语句将值直接写入文件。这里有一个示例：
SELECT * FROM (VALUES ROW(1,2,3),ROW(4,5,6),ROW(7,8,9)) AS t
INTO OUTFILE '/tmp/select-values.txt';
您必须使用表别名；列别名也受支持，并且可以选择性地用于仅从所需列中写入值。您还可以使用 SELECT ... INTO OUTFILE 支持的任何或所有导出选项来将输出格式化到文件中。
这里有一个示例，生成一个以逗号分隔值（CSV）格式的文件，许多程序使用这种格式：
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
如果使用 INTO DUMPFILE 而不是 INTO OUTFILE，MySQL 只会将一行写入文件，不会有任何列或行终止，并且不执行任何转义处理。这对于选择 BLOB 值并将其存储在文件中很有用。
TABLE也支持INTO DUMPFILE。 如果表包含多行，则还必须使用LIMIT 1将输出限制为单行。 INTO DUMPFILE也可以与SELECT * FROM (VALUES ROW()[, ...]) AS *table_alias* [LIMIT 1]一起使用。请参阅 Section 15.2.19，“VALUES Statement”。
注意
由INTO OUTFILE或INTO DUMPFILE创建的任何文件都归属于运行mysqld的操作系统用户。 （出于这个和其他原因，您绝对不应该以root身份运行mysqld。）从 MySQL 8.0.17 开始，文件创建的 umask 为 0640；您必须具有足够的访问权限来操作文件内容。在 MySQL 8.0.17 之前，umask 为 0666，文件可被服务器主机上的所有用户写入。
如果secure_file_priv系统变量设置为非空目录名称，则要写入的文件必须位于该目录中。
在由事件调度程序执行的事件的一部分中发生的SELECT ... INTO语句的上下文中，诊断消息（不仅是错误，还包括警告）将被写入错误日志，并且在 Windows 上，将被写入应用程序事件日志。有关更多信息，请参阅 Section 27.4.5，“事件调度程序状态”。
从 MySQL 8.0.22 开始，支持定期同步由SELECT INTO OUTFILE和SELECT INTO DUMPFILE写入的输出文件，通过设置在该版本中引入的select_into_disk_sync服务器系统变量来启用。可以使用select_into_buffer_size和select_into_disk_sync_delay分别设置输出缓冲区大小和可选延迟。有关更多信息，请参阅这些系统变量的描述。
15.2.13.2 连接子句
MySQL 支持以下JOIN语法用于SELECT语句的*table_references*部分以及多表DELETE和UPDATE语句：
*table_references:*
*escaped_table_reference* [, *escaped_table_reference*] ...
*escaped_table_reference*: {
*table_reference*
| { OJ *table_reference* }
}
*table_reference*: {
*table_factor*
| *joined_table*
}
*table_factor*: {
*tbl_name* [PARTITION (*partition_names*)]
[[AS] *alias*] [*index_hint_list*]
| [LATERAL] *table_subquery* [AS] *alias* [(*col_list*)]
| ( *table_references* )
}
*joined_table*: {
*table_reference* {[INNER | CROSS] JOIN | STRAIGHT_JOIN} *table_factor* [*join_specification*]
| *table_reference* {LEFT|RIGHT} [OUTER] JOIN *table_reference* *join_specification*
| *table_reference* NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN *table_factor*
}
*join_specification*: {
ON *search_condition*
| USING (*join_column_list*)
}
*join_column_list*:
*column_name* [, *column_name*] ...
*index_hint_list*:
*index_hint* [, *index_hint*] ...
*index_hint*: {
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([*index_list*])
| {IGNORE|FORCE} {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (*index_list*)
}
*index_list*:
*index_name* [, *index_name*] ...
表引用也被称为连接表达式。
表引用（当它引用分区表时）可以包含一个PARTITION子句，包括一个逗号分隔的分区、子分区列表，或两者。此选项跟随表名之后，并在任何别名声明之前。此选项的效果是仅从列出的分区或子分区中选择行。未在列表中命名的任何分区或子分区将被忽略。有关更多信息和示例，请参见第 26.5 节，“分区选择”。
与标准 SQL 相比，MySQL 中*table_factor的语法得到了扩展。标准只接受table_reference*，而不是在括号中包含它们的列表。
如果将*table_reference*项目列表中的每个逗号视为等同于内连接，则这是一种保守的扩展。例如：
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
等同于：
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
在 MySQL 中，JOIN、CROSS JOIN和INNER JOIN是语法上的等效（它们可以互相替换）。在标准 SQL 中，它们不是等效的。INNER JOIN与ON子句一起使用，否则使用CROSS JOIN。
通常情况下，在仅包含内连接操作的连接表达式中，括号可以忽略。MySQL 还支持嵌套连接。参见第 10.2.1.8 节，“嵌套连接优化”。
可以指定索引提示以影响 MySQL 优化器如何使用索引。有关更多信息，请参见第 10.9.4 节，“索引提示”。优化器提示和optimizer_switch系统变量是影响优化器使用索引的其他方法。请参见第 10.9.3 节，“优化器提示”和第 10.9.2 节，“可切换优化”。
下面的列表描述了编写连接时需要考虑的一般因素：
-
可以使用
*tbl_name* AS *alias_name*或*tbl_name alias_name*为表引用取别名：SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name; -
table_subquery也被称为FROM子句中的派生表或子查询。参见第 15.2.15.8 节，“派生表”。这样的子查询必须包含一个别名，以给子查询结果一个表名，并且可以选择在括号中包含一个表列名列表。以下是一个简单的示例：SELECT * FROM (SELECT 1, 2, 3) AS t1; -
在单个连接中引用的最大表数为 61。这包括通过将
FROM子句中的派生表和视图合并到外部查询块中处理的连接（参见 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”）。 -
在没有连接条件的情况下，
INNER JOIN和,（逗号）在语义上是等效的：两者都会在指定的表之间产生笛卡尔积（即，第一个表中的每一行都与第二个表中的每一行连接）。然而，逗号运算符的优先级低于
INNER JOIN、CROSS JOIN、LEFT JOIN等。如果在存在连接条件时混合使用逗号连接和其他连接类型，则可能会出现类似Unknown column '*col_name*' in 'on clause'的错误。有关处理此问题的信息稍后在本节中给出。 -
与
ON一起使用的search_condition是可以在WHERE子句中使用的任何条件表达式的形式。通常，ON子句用于指定如何连接表，而WHERE子句用于限制结果集中包含哪些行。 -
如果在
LEFT JOIN中右表的ON或USING部分中没有匹配的行，则会使用所有列均设置为NULL的行作为右表。您可以利用这一点找到一个表中没有对应的另一个表中的行：SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;此示例查找
left_tbl中所有具有不在right_tbl中存在的id值的行（即，所有在right_tbl中没有对应行的left_tbl中的所有行）。参见 Section 10.2.1.9, “Outer Join Optimization”。 -
USING(*join_column_list*)子句命名了两个表中必须存在的列的列表。如果表a和b都包含列c1、c2和c3，则以下连接将比较来自两个表的对应列：a LEFT JOIN b USING (c1, c2, c3) -
两个表的
NATURAL [LEFT] JOIN被定义为与使用命名了两个表中所有列的USING子句的INNER JOIN或LEFT JOIN在���义上等效。 -
RIGHT JOIN的工作方式类似于LEFT JOIN。为了保持代码在各种数据库中的可移植性，建议您使用LEFT JOIN而不是RIGHT JOIN。 -
在连接语法描述中显示的
{ OJ ... }语法仅用于与 ODBC 的兼容性。语法中的大括号应该按照字面意义写入；它们不是在其他语法描述中使用的元语法。SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL;您可以在
{ OJ ... }中使用其他类型的连接，例如INNER JOIN或RIGHT OUTER JOIN。这有助于与一些第三方应用程序的兼容性，但不是官方的 ODBC 语法。 -
STRAIGHT_JOIN类似于JOIN，不同之处在于左表始终在右表之前读取。这可以用于那些（少数）情况下，连接优化器以次优顺序处理表的情况。
一些连接示例：
SELECT * FROM table1, table2;
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
SELECT * FROM table1 LEFT JOIN table2 USING (id);
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.id = table3.id;
根据 SQL:2003 标准处理NATURAL连接和带有USING的连接，包括外连接变体：
-
NATURAL连接的冗余列不会出现。考虑以下一组语句：CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);在第一个
SELECT语句中，列j出现在两个表中，因此成为连接列，因此，根据标准 SQL，它应该在输出中只出现一次，而不是两次。类似地，在第二个 SELECT 语句中，列j在USING子句中命名，应该在输出中只出现一次，而不是两次。因此，这些语句产生这个输出：
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+根据标准 SQL 进行冗余列消除和列排序，产生这个显示顺序：
-
首先，按照它们在第一个表中出现的顺序，合并两个连接表的共同列
-
第二，第一个表中独有的列，按照它们在该表中出现的顺序
-
第三，第二个表中独有的列，按照它们在该表中出现的顺序
替换两个共同列的单个结果列是使用合并操作定义的。也就是说，对于两个
t1.a和t2.a，生成的单个连接列a被定义为a = COALESCE(t1.a, t2.a)，其中：COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)如果连接操作是任何其他连接，则连接的结果列由连接表的所有列的串联组成。
合并列的定义的一个结果是，对于外连接，如果两个列中的一个始终为
NULL，则合并列包含非NULL列的值。如果两个列都不是NULL或都是NULL，那么两个共同列具有相同的值，因此选择哪个作为合并列的值并不重要。解释这个的一个简单方法是将外连接的合并列表示为JOIN的内表的共同列。假设表t1(a, b)和表t2(a, c)具有以下内容：t1 t2 ---- ---- 1 x 2 z 2 y 3 w然后，对于这个连接，列
a包含t1.a的值：mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2; +------+------+------+ | a | b | c | +------+------+------+ | 1 | x | NULL | | 2 | y | z | +------+------+------+相比之下，对于这个连接，列
a包含t2.a的值。mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2; +------+------+------+ | a | c | b | +------+------+------+ | 2 | z | y | | 3 | w | NULL | +------+------+------+将这些结果与使用
JOIN ... ON的等效查询进行比较：mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a); +------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 1 | x | NULL | NULL | | 2 | y | 2 | z | +------+------+------+------+mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a); +------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 2 | y | 2 | z | | NULL | NULL | 3 | w | +------+------+------+------+ -
-
USING子句可以重写为比较相应列的ON子句。然而，尽管USING和ON类似，但它们并不完全相同。考虑以下两个查询：a LEFT JOIN b USING (c1, c2, c3) a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3就确定哪些行满足连接条件而言，这两个连接在语义上是相同的。
关于确定要显示哪些列进行
SELECT *扩展，这两个连接在语义上并不相同。USING连接选择对应列的合并值，而ON连接选择所有表中的所有列。对于USING连接，SELECT *选择这些值：COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)对于
ON连接，SELECT *选择这些值：a.c1, a.c2, a.c3, b.c1, b.c2, b.c3在内连接中，
COALESCE(a.c1, b.c1)与a.c1或b.c1相同，因为两列的值相同。在外连接（如LEFT JOIN）中，两列中的一个可以是NULL。该列将从结果中省略。 -
ON子句只能引用其操作数。示例：
CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;该语句因为
i3是t3中的列，而不是ON子句的操作数而失败，会出现Unknown column 'i3' in 'on clause'错误。要使连接能够被处理，请将语句重写如下：SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3); -
JOIN比逗号运算符（,）具有更高的优先级，因此连接表达式t1, t2 JOIN t3被解释为(t1, (t2 JOIN t3))，而不是((t1, t2) JOIN t3)。这会影响使用ON子句的语句，因为该子句只能引用连接操作数中的列，而优先级会影响这些操作数的解释。示例：
CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); INSERT INTO t3 VALUES(1, 1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);JOIN优先于逗号运算符，因此ON子句的操作数为t2和t3。因为t1.i1不是任何操作数中的列，结果是一个Unknown column 't1.i1' in 'on clause'错误。要使连接能够被处理，可以使用以下策略之一：
-
使用括号明确地将前两个表分组，以便
ON子句的操作数为(t1, t2)和t3：SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); -
避免使用逗号运算符，改用
JOIN代替：SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
相同的优先级解释也适用于混合逗号运算符与
INNER JOIN、CROSS JOIN、LEFT JOIN和RIGHT JOIN的语句，所有这些连接比逗号运算符具有更高的优先级。 -
-
与 SQL:2003 标准相比，MySQL 的一个扩展是允许您对
NATURAL或USING连接的共同（合并的）列进行限定，而标准则不允许。
15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合操作
-
结果集列名和数据类型
-
使用 TABLE 和 VALUES 语句进行集合操作
-
使用 DISTINCT 和 ALL 的集合操作
-
使用 ORDER BY 和 LIMIT 的集合操作
-
集合操作的限制
SQL 集合操作将多个查询块的结果合并为单个结果。查询块，有时也称为简单表，是任何返回结果集的 SQL 语句，例如 SELECT。MySQL 8.0（8.0.19 及更高版本）还支持 TABLE 和 VALUES 语句。有关这些语句的详细信息，请参见本章其他部分中的各自描述。
SQL 标准定义了以下三种集合操作：
-
UNION：将两个查询块的所有结果合并为单个结果，省略任何重复项。 -
INTERSECT：仅合并两个查询块结果中共有的行，省略任何重复项。 -
EXCEPT：对于两个查询块A和B，返回A中不在B中出现的所有结果，省略任何重复项。（一些数据库系统，如 Oracle，使用
MINUS作为此运算符的名称。MySQL 不支持此功能。）
MySQL 长期支持 UNION；MySQL 8.0 添加了对 INTERSECT 和 EXCEPT 的支持（MySQL 8.0.31 及更高版本）。
每个集合运算符都支持 ALL 修饰符。当 ALL 关键字跟随一个集合运算符时，这会导致结果中包含重复项。有关更多信息和示例，请参阅涵盖各个运算符的以下部分。
所有三个集合运算符还支持 DISTINCT 关键字，用于在结果中消除重复项。由于这是集合运算符的默认行为，通常不需要显式指定 DISTINCT。
一般来说，查询块和集合操作可以以任意数量和顺序组合。这里展示了一个大大简化的表示：
*query_block* [*set_op* *query_block*] [*set_op* *query_block*] ...
*query_block*:
SELECT | TABLE | VALUES
*set_op*:
UNION | INTERSECT | EXCEPT
这可以更准确地表示，并更详细地描述如下：
*query_expression*:
[*with_clause*] /* WITH clause */
*query_expression_body*
[*order_by_clause*] [*limit_clause*] [*into_clause*]
*query_expression_body*:
*query_term*
| *query_expression_body* UNION [ALL | DISTINCT] *query_term*
| *query_expression_body* EXCEPT [ALL | DISTINCT] *query_term*
*query_term*:
*query_primary*
| *query_term* INTERSECT [ALL | DISTINCT] *query_primary*
*query_primary*:
*query_block*
| '(' *query_expression_body* [*order_by_clause*] [*limit_clause*] [*into_clause*] ')'
*query_block*: /* also known as a simple table */
*query_specification* /* SELECT statement */
| *table_value_constructor* /* VALUES statement */
| *explicit_table* /* TABLE statement */
您应该知道INTERSECT在UNION或EXCEPT之前进行评估。这意味着，例如，TABLE x UNION TABLE y INTERSECT TABLE z总是被评估为TABLE x UNION (TABLE y INTERSECT TABLE z)。有关更多信息，请参见第 15.2.8 节，“INTERSECT 子句”。
此外，您应该记住，虽然UNION和INTERSECT集合运算符是可交换的（顺序不重要），但EXCEPT不是（操作数的顺序会影响结果）。换句话说，以下所有语句都是正确的：
-
TABLE x UNION TABLE y和TABLE y UNION TABLE x产生相同的结果，尽管行的排序可能不同。您可以使用ORDER BY强制它们相同；请参见联合中的 ORDER BY 和 LIMIT。 -
TABLE x INTERSECT TABLE y和TABLE y INTERSECT TABLE x返回相同的结果。 -
TABLE x EXCEPT TABLE y和TABLE y EXCEPT TABLE x不会产生相同的结果。请参见第 15.2.4 节，“EXCEPT 子句”，以获取示例。
更多信息和示例可以在接下来的章节中找到。
结果集列名和数据类型
集合操作的结果的列名取自第一个查询块的列名。示例：
mysql> CREATE TABLE t1 (x INT, y INT);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t1 VALUES ROW(4,-2), ROW(5,9);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t2 (a INT, b INT);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t2 VALUES ROW(1,2), ROW(3,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> TABLE t1 UNION TABLE t2;
+------+------+
| x | y |
+------+------+
| 4 | -2 |
| 5 | 9 |
| 1 | 2 |
| 3 | 4 |
+------+------+
4 rows in set (0.00 sec)
mysql> TABLE t2 UNION TABLE t1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 4 | -2 |
| 5 | 9 |
+------+------+
4 rows in set (0.00 sec)
对于UNION、EXCEPT和INTERSECT查询都是如此。
每个查询块中列的选定位置应具有相同的数据类型。例如，第一个语句选择的第一列应与其他语句选择的第一列具有相同的类型。如果相应结果列的数据类型不匹配，则结果中的列的类型和长度将考虑所有查询块检索的值。例如，结果集中的列长度不受限于第一个语句中的值的长度，如下所示：
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1) |
+----------------------+
| a |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+
使用TABLE和VALUES语句进行集合操作
从 MySQL 8.0.19 开始，您还可以在可以使用等效的SELECT语句的地方使用TABLE语句或VALUES语句。假设表t1和t2如下所示创建和填充：
CREATE TABLE t1 (x INT, y INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);
在忽略以VALUES开头的查询输出中的列名的情况下，以下所有UNION查询都产生相同的结果：
SELECT * FROM t1 UNION SELECT * FROM t2;
TABLE t1 UNION SELECT * FROM t2;
VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
SELECT * FROM t1 UNION TABLE t2;
TABLE t1 UNION TABLE t2;
VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);
要强制列名相同，请将左侧的查询块包装在SELECT语句中，并使用别名，如下所示：
mysql> SELECT * FROM (TABLE t2) AS t(x,y) UNION TABLE t1;
+------+------+
| x | y |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 4 | -2 |
| 5 | 9 |
+------+------+
4 rows in set (0.00 sec)
使用DISTINCT和ALL进行集合操作
默认情况下，集合操作的结果中会删除重复行。可选的DISTINCT关键字具有相同的效果，但使其显式化。使用可选的ALL关键字，不会删除重复行，结果将包含联合中所有查询的所有匹配行。
你可以在同一查询中混合使用ALL和DISTINCT。混合类型的处理方式是，使用DISTINCT的集合操作会覆盖左侧使用ALL的任何操作。可以通过在UNION、INTERSECT或EXCEPT后显式地使用DISTINCT，或者在没有跟随DISTINCT或ALL关键字的情况下隐式地使用集合操作来生成DISTINCT集合。
在 MySQL 8.0.19 及更高版本中，当一个或多个TABLE语句、VALUES语句或两者用于生成集合时，集合操作的工作方式相同。
使用ORDER BY和LIMIT的集合操作
要对作为联合、交集或其他集合操作的一部分使用的单个查询块应用ORDER BY或LIMIT子句，请将查询块括在括号中，并将子句放在括号内，就像这样：
(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);
(TABLE t1 ORDER BY x LIMIT 10)
INTERSECT
(TABLE t2 ORDER BY a LIMIT 10);
对于单个查询块或语句使用ORDER BY并不意味着结果中行的顺序，因为默认情况下，集合操作生成的行是无序的。因此，在这种情况下，ORDER BY通常与LIMIT结合使用，以确定要检索的所选行的子集，即使它并不一定影响这些行在最终结果中的顺序。如果在查询块中没有LIMIT出现ORDER BY，则会被优化掉，因为在任何情况下都没有影响。
要对整个集合操作的结果进行排序或限制，请将ORDER BY或LIMIT放在最后一个语句之后：
SELECT a FROM t1
EXCEPT
SELECT a FROM t2 WHERE a=11 AND b=2
ORDER BY a LIMIT 10;
TABLE t1
UNION
TABLE t2
ORDER BY a LIMIT 10;
如果一个或多个单独的语句使用了ORDER BY、LIMIT或两者，并且另外，你希望对整个结果应用ORDER BY、LIMIT或两者，则必须将每个这样的单独语句括在括号中。
(SELECT a FROM t1 WHERE a=10 AND b=1)
EXCEPT
(SELECT a FROM t2 WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;
(TABLE t1 ORDER BY a LIMIT 10)
UNION
TABLE t2
ORDER BY a LIMIT 10;
没有ORDER BY或LIMIT子句的语句不需要括号；在刚刚显示的两个语句的第二个语句中用(TABLE t2)替换TABLE t2不会改变UNION的结果。
你也可以在集合操作中使用ORDER BY和LIMIT，就像在这个使用mysql客户端的示例中所示的那样：
mysql> VALUES ROW(4,-2), ROW(5,9), ROW(-1,3)
-> UNION
-> VALUES ROW(1,2), ROW(3,4), ROW(-1,3)
-> ORDER BY column_0 DESC LIMIT 3;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
| 5 | 9 |
| 4 | -2 |
| 3 | 4 |
+----------+----------+
3 rows in set (0.00 sec)
（请记住，TABLE语句和VALUES语句都不接受WHERE子句。）
这种类型的ORDER BY不能使用包含表名的列引用（即以*tbl_name.col_name*格式的名称）。相反，在第一个查询块中提供一个列别名，并在ORDER BY子句中引用该别名。 （你也可以在ORDER BY子句中使用列位置引用该列，但这种列位置的使用已被弃用，因此可能在未来的 MySQL 版本中被移除。）
如果要排序的列被别名，ORDER BY子句必须引用别名，而不是列名。以下两个语句中第一个是允许的，但第二个会因为Unknown column 'a' in 'order clause'错误而失败：
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
为了使UNION结果的行由每个查询块检索的行集合依次组成，需要在每个查询块中选择一个额外的列作为排序列，并在最后一个查询块后添加一个按照该列排序的ORDER BY子句：
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
为了在各个结果中保持排序顺序，向ORDER BY子句添加一个次要列：
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
使用额外的列还可以让你确定每行来自哪个查询块。额外的列还可以提供其他标识信息，比如指示表名的字符串。
集合操作的限制
MySQL 中的集合操作受到一些限制，这些限制在接下来的几段中描述。
包括SELECT语句在内的集合操作有以下限制：
-
第一个
SELECT中的HIGH_PRIORITY没有效果。任何后续SELECT中的HIGH_PRIORITY都会产生语法错误。 -
仅最后一个
SELECT语句可以使用INTO子句。然而，整个UNION结果将被写入INTO输出目的地。
截至 MySQL 8.0.20，这两个包含INTO的UNION变体已被弃用；你应该期待它们在未来的 MySQL 版本中被移除的支持：
-
在查询表达式的尾随查询块中，在
FROM之前使用INTO会产生警告。例如：... UNION SELECT * INTO OUTFILE '*file_name*' FROM *table_name*; -
在查询表达式的括号尾随块中，使用
INTO（无论其相对于FROM的位置如何）都会产生警告。例如：... UNION (SELECT * INTO OUTFILE '*file_name*' FROM *table_name*);这些变体已经被弃用，因为它们很令人困惑，好像它们收集的信息来自命名表而不是整个查询表达式（
UNION）。
在ORDER BY子句中使用聚合函数的集合操作将被拒绝，并显示ER_AGGREGATE_ORDER_FOR_UNION。虽然错误名称可能暗示这仅适用于UNION查询，但前述情况也适用于EXCEPT和INTERSECT查询，如下所示：
mysql> TABLE t1 INTERSECT TABLE t2 ORDER BY MAX(x);
ERROR 3028 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT
锁定子句（比如FOR UPDATE或LOCK IN SHARE MODE）适用于其后的查询块。这意味着，在与集合操作一起使用的SELECT语句中，只有在查询块和锁定子句被括号括起来时才能使用锁定子句。
15.2.15 子查询
15.2.15.1 子查询作为标量操作数
15.2.15.2 使用子查询进行比较
15.2.15.3 使用 ANY、IN 或 SOME 的子查询
15.2.15.4 使用 ALL 的子查询
15.2.15.5 行子查询
15.2.15.6 使用 EXISTS 或 NOT EXISTS 的子查询
15.2.15.7 相关子查询
15.2.15.8 派生表
15.2.15.9 横向派生表
15.2.15.10 子查询错误
15.2.15.11 优化子查询
15.2.15.12 子查询的限制
子查询是另一个语句内的SELECT语句。
所有 SQL 标准要求的子查询形式和操作都得到支持，以及一些 MySQL 特有的功能。
这里是一个子查询的示例：
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)));
子查询可以返回标量（单个值）、单行、单列或表（一个或多个列的一个或多行）。这些称为标量、列、行和表子查询。通常只能在特定上下文中使用返回特定类型结果的子查询，如下节所述。
子查询可以在哪些类型的语句中使用没有太多限制。子查询可以包含许多普通SELECT可以包含的关键字或子句：DISTINCT、GROUP BY、ORDER BY、LIMIT、连接、索引提示、UNION构造、注释、函数等等。
从 MySQL 8.0.19 开始，TABLE和VALUES语句可以在子查询中使用。使用VALUES的子查询通常是更冗长的子查询版本，可以使用集合表示法更简洁地重写，或者使用SELECT或TABLE语法；假设表ts是使用语句CREATE TABLE ts VALUES ROW(2), ROW(4), ROW(6)创建的，这里显示的语句都是等效的：
SELECT * FROM tt
WHERE b > ANY (VALUES ROW(2), ROW(4), ROW(6));
SELECT * FROM tt
WHERE b > ANY (SELECT * FROM ts);
SELECT * FROM tt
WHERE b > ANY (TABLE ts);
TABLE子查询的示例将在接下来的章节中展示。
子查询的外部语句可以是任何一个：SELECT, INSERT, UPDATE, DELETE, SET, 或 DO。
有关优化器如何处理子查询的信息，请参阅第 10.2.2 节，“优化子查询、派生表、视图引用和公共表达式”。有关子查询使用的限制讨论，包括某些形式子查询语法的性能问题，请参阅第 15.2.15.12 节，“子查询的限制”。
15.2.15.1 标量操作数的子查询
在其最简单的形式中，子查询是返回单个值的标量子查询。标量子查询是一个简单的操作数，你几乎可以在任何地方使用它，只要单列值或字面值是合法的，并且你可以期望它具有所有操作数具有的特征：数据类型、长度、可以为NULL的指示等。例如：
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);
这个SELECT中的子查询返回一个单一值（'abcde'），其数据类型为CHAR，长度为 5，字符集和排序规则等于CREATE TABLE时生效的默认值，并指示列中的值可以为NULL。标量子查询选择的值的可空性不会被复制，因为如果子查询结果为空，结果就是NULL。对于刚刚显示的子查询，如果t1为空，结果将是NULL，即使s2是NOT NULL。
在一些情况下，标量子查询无法使用。如果语句只允许使用字面值，你就不能使用子查询。例如，LIMIT需要字面整数参数，而LOAD DATA需要字面字符串文件名。你不能使用子查询来提供这些值。
当你在以下部分看到包含相当简陋结构(SELECT column1 FROM t1)的示例时，请想象你自己的代码包含更加多样化和复杂的结构。
假设我们创建了两个表：
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;
结果是2，因为t2中有一行包含一个值为2的列s1。
在 MySQL 8.0.19 及更高版本中，前面的查询也可以这样写，使用TABLE：
SELECT (TABLE t2) FROM t1;
标量子查询可以是表达式的一部分，但记住括号，即使子查询是为函数提供参数的操作数。例如：
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;
在 MySQL 8.0.19 及更高版本中，可以使用SELECT UPPER((TABLE t1)) FROM t2获得相同的结果。
原文：
dev.mysql.com/doc/refman/8.0/en/comparisons-using-subqueries.html
15.2.15.2 使用子查询进行比较
子查询最常见的用法是形式：
*non_subquery_operand* *comparison_operator* (*subquery*)
其中*comparison_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);
对于将子查询与标量进行比较，子查询必须返回一个标量。对于将子查询与行构造函数进行比较，子查询必须是返回与行构造函数相同数量值的行子查询。参见第 15.2.15.5 节，“行子查询”。
原文：
dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html
15.2.15.3 带有 ANY、IN 或 SOME 的子查询
语法：
*operand* *comparison_operator* ANY (*subquery*)
*operand* IN (*subquery*)
*operand* *comparison_operator* SOME (*subquery*)
其中*comparison_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)，则表达式为unknown（即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不能。参见 Section 14.4.2, “Comparison Functions and Operators”。
NOT IN不是<> ANY的别名，而是<> ALL的别名。参见 Section 15.2.15.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 语法的意思。该语法的含义是“有一些 b 不等于 a”。使用<> SOME可以确保每个人都理解查询的真正含义。
从 MySQL 8.0.19 开始，您可以在标量IN、ANY或SOME子查询中使用TABLE，前提是表只包含一列。如果t2只有一列，那么本节中先前显示的语句可以写成这样，在每种情况下用TABLE t2替换SELECT s1 FROM t2：
SELECT s1 FROM t1 WHERE s1 > ANY (TABLE t2);
SELECT s1 FROM t1 WHERE s1 = ANY (TABLE t2);
SELECT s1 FROM t1 WHERE s1 IN (TABLE t2);
SELECT s1 FROM t1 WHERE s1 <> ANY (TABLE t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (TABLE t2);
15.2.15.4 带有 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)，表达式为 unknown（即 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);
MySQL 8.0.19 支持 TABLE 语句。与 IN、ANY 和 SOME 一样，你可以在 TABLE 中使用 ALL 和 NOT IN，前提是满足以下两个条件：
-
子查询中只包含一列
-
子查询不依赖于列表达式
例如，假设表 t2 只包含一列，前面显示的最后两个语句可以这样使用 TABLE t2 编写：
SELECT s1 FROM t1 WHERE s1 <> ALL (TABLE t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (TABLE t2);
无法使用 TABLE t2 编写诸如 SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2); 这样的查询，因为子查询依赖于列表达式。
15.2.15.5 行子查询
标量或列子查询返回单个值或一列值。行子查询是一种返回单行的子查询变体，因此可以返回多个列值。行子查询比较的合法运算符有：
= > < >= <= <> != <=>
以下是两个示例：
SELECT * FROM t1
WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
对于这两个查询，如果表t2包含一个id = 10的单行，子查询返回一个单行。如果此行的col3和col4值等于任何t1行的col1和col2值，则WHERE表达式为TRUE，每个查询都返回这些t1行。如果t2行的col3和col4值不等于任何t1行的col1和col2值，则表达式为FALSE，查询返回一个空结果集。如果子查询未产生行，则表达式为未知（即NULL）。如果子查询产生多行，则会出现错误，因为行子查询最多只能返回一行。
有关每个运算符如何用于行比较的信息，请参阅第 14.4.2 节，“比较函数和运算符”。
表达式(1,2)和ROW(1,2)有时被称为行构造器。这两者是等价的。子查询返回的行构造器和行必须包含相同数量的值。
行构造器用于与返回两个或更多列的子查询进行比较。当子查询返回单列时，这被视为标量值而不是行，因此不能将行构造器与不返回至少两列的子查询一起使用。因此，以下查询由于语法错误而失败：
SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)
行构造器在其他情境下也是合法的。例如，以下两个语句在语义上是等价的（并且由优化器以相同方式处理）：
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
以下查询回答了请求，“找到表t1中存在于表t2中的所有行”：
SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);
有关优化器和行构造器的更多信息，请参阅第 10.2.1.22 节，“行构造器表达式优化”
原文：
dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
15.2.15.6 带有 EXISTS 或 NOT EXISTS 的子查询
如果子查询返回任何行，EXISTS *子查询* 是 TRUE，而 NOT EXISTS *子查询* 是 FALSE。例如：
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
传统上，EXISTS 子查询以 SELECT * 开头，但也可以以 SELECT 5 或 SELECT column1 或任何其他内容开头。MySQL 会忽略这种子查询中的 SELECT 列表，因此不会有任何区别。
对于前面的示例，如果 t2 包含任何行，即使行中只有 NULL 值，EXISTS 条件也为 TRUE。这实际上是一个不太可能的示例，因为 [NOT] EXISTS 子查询几乎总是包含相关性。以下是一些更现实的示例：
-
一个或多个城市中存在什么样的商店？
SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type); -
没有城市中存在什么样的商店？
SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type); -
所有城市中存在什么样的商店？
SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS ( SELECT * FROM cities WHERE NOT EXISTS ( SELECT * FROM cities_stores WHERE cities_stores.city = cities.city AND cities_stores.store_type = stores.store_type));
最后一个示例是一个双重嵌套的 NOT EXISTS 查询。也就是说，它在一个 NOT EXISTS 子句中有一个 NOT EXISTS 子句。形式上，它回答了“是否存在一个城市有一个不在 Stores 中的商店”这个问题？但更容易说的是，嵌套的 NOT EXISTS 回答了“对于所有 y，x 是否都为 TRUE？”
在 MySQL 8.0.19 及更高版本中，您还可以在子查询中使用 NOT EXISTS 或 NOT EXISTS 与 TABLE，就像这样：
SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);
结果与在子查询中没有 WHERE 子句的情况下使用 SELECT * 相同。
原文：
dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html
15.2.15.7 相关子查询
相关子查询 是一个包含对外部查询中也出现的表的引用的子查询。例如：
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
注意，子查询包含对 t1 列的引用，即使子查询的 FROM 子句没有提及表 t1。因此，MySQL 查找子查询外部，在外部查询中找到 t1。
假设表 t1 包含一行，其中 column1 = 5 和 column2 = 6；同时，表 t2 包含一行，其中 column1 = 5 和 column2 = 7。简单表达式 ... WHERE column1 = ANY (SELECT column1 FROM t2) 将是 TRUE，但在这个例子中，子查询中的 WHERE 子句是 FALSE（因为 (5,6) 不等于 (5,7)），因此整个表达式是 FALSE。
作用域规则： MySQL 从内到外进行评估。例如：
SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));
在这个语句中，x.column2 必须是表 t2 中的一列，因为 SELECT column1 FROM t2 AS x ... 重命名了 t2。它不是表 t1 中的一列，因为 SELECT column1 FROM t1 ... 是一个更远处的外部查询。
从 MySQL 8.0.24 开始，当 optimizer_switch 变量的 subquery_to_derived 标志启用时，优化器可以将相关标量子查询转换为派生表。考虑这里显示的查询：
SELECT * FROM t1
WHERE ( SELECT a FROM t2
WHERE t2.a=t1.a ) > 0;
为了避免为给定的派生表多次实例化，我们可以代替多次实例化一个派生表，该派生表在内部查询中引用的表（t2.a）上添加一个分组，然后在提升的谓词（t1.a = derived.a）上进行外连接，以选择正确的组与外部行匹配。 （如果子查询已经有明确的分组，则额外的分组将添加到分组列表的末尾。）因此，先前显示的查询可以像这样重写：
SELECT t1.* FROM t1
LEFT OUTER JOIN
(SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived
ON t1.a = derived.a
AND
REJECT_IF(
(ct > 1),
"ERROR 1242 (21000): Subquery returns more than 1 row"
)
WHERE derived.a > 0;
在重写的查询中，REJECT_IF() 表示一个内部函数，用于测试给定条件（这里是比较 ct > 1）并在条件为真时引发给定错误（在本例中是 ER_SUBQUERY_NO_1_ROW）。这反映了优化器在评估 JOIN 或 WHERE 子句之前执行的基数检查，之后才评估任何提升的谓词，只有在子查询不返回多于一行时才执行。
只有满足以下条件时，才能执行这种类型的转换：
-
子查询可以是
SELECT列表、WHERE条件或HAVING条件的一部分，但不能是JOIN条件的一部分，并且不能包含LIMIT或OFFSET子句。此外，子查询不能包含任何集合操作，如UNION。 -
WHERE子句可以包含一个或多个谓词，并用AND组合。如果WHERE子句包含一个OR子句，则无法进行转换。WHERE子句中至少有一个谓词必须符合转换条件，且没有一个谓词可以拒绝转换。 -
要符合转换的条件，
WHERE子句谓词必须是一个等式谓词，其中每个操作数都应该是一个简单的列引用。没有其他谓词—包括其他比较谓词—符合转换条件。该谓词必须使用等号操作符=进行比较；在这种情况下，不支持空安全≪=>操作符。 -
只包含内部引用的
WHERE子句谓词不符合转换条件，因为它可以在分组之前进行评估。只包含外部引用的WHERE子句谓词符合转换条件，即使它可以提升到外部查询块。这是通过在派生表中添加一个不带分组的基数检查来实现的。 -
要符合条件，
WHERE子句谓词必须有一个操作数仅包含内部引用，另一个操作数仅包含外部引用。如果由于此规则而使谓词不符合条件，则拒绝转换查询。 -
相关列只能存在于子查询的
WHERE子句中（而不是SELECT列表、JOIN或ORDER BY子句、GROUP BY列表或HAVING子句）。子查询的FROM列表中也不能有任何相关列。 -
相关列不能包含在聚合函数的参数列表中。
-
相关列必须在直接包含待转换子查询的查询块中解析。
-
在
WHERE子句中的嵌套标量子查询中不能存在相关列。 -
子查询不能包含任何窗口函数，并且不能包含在子查询外部的查询块中聚合的任何聚合函数。如果
SELECT列表元素中包含COUNT()聚合函数，则必须在最高级别，并且不能是表达式的一部分。
另请参阅第 15.2.15.8 节，“派生表”。
15.2.15.8 派生表
本节讨论了派生表的一般特性。有关由LATERAL关键字引导的横向派生表的信息，请参见 Section 15.2.15.9，“横向派生表”。
派生表是在查询FROM子句的范围内生成表的表达式。例如，在SELECT语句的FROM子句中的子查询是一个派生表：
SELECT ... FROM (*subquery*) [AS] *tbl_name* ...
JSON_TABLE()函数生成一个表，并提供了创建派生表的另一种方法：
SELECT * FROM JSON_TABLE(*arg_list*) [AS] *tbl_name* ...
[AS] *tbl_name*子句是必需的，因为FROM子句中的每个表都必须有一个名称。派生表中的任何列必须具有唯一的名称。或者，tbl_name`后面可以跟着一个括号括起来的列名列表：
SELECT ... FROM (*subquery*) [AS] *tbl_name* (*col_list*) ...
列名的数量必须与表列的数量相同。
为了说明问题，假设您有这个表：
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
下面是如何在FROM子句中使用子查询，使用示例表：
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;
结果：
+------+------+------+
| sb1 | sb2 | sb3 |
+------+------+------+
| 2 | 2 | 4 |
+------+------+------+
这里是另一个例子：假设您想知道一个分组表的一组求和的平均值。这不起作用：
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
然而，这个查询提供了所需的信息：
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
注意，在子查询中使用的列名（sum_column1）在外部查询中被识别。
派生表的列名来自其选择列表：
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
要明确提供列名，请在派生表名称后面跟着一个括号括起来的列名列表：
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
派生表可以返回标量、列、行或表。
派生表受到以下限制：
-
派生表不能包含对同一
SELECT的其他表的引用（使用LATERAL派生表进行处理；请参见 Section 15.2.15.9，“横向派生表”）。 -
在 MySQL 8.0.14 之前，派生表不能包含外部引用。这是 MySQL 在 MySQL 8.0.14 中解除的限制，而不是 SQL 标准的限制。例如，以下查询中的派生表
dt包含对外部查询中表t1的引用t1.b：SELECT * FROM t1 WHERE t1.d > (SELECT AVG(dt.a) FROM (SELECT SUM(t2.a) AS a FROM t2 WHERE t2.b = t1.b GROUP BY t2.c) dt WHERE dt.a > 10);该查询在 MySQL 8.0.14 及更高版本中有效。在 8.0.14 之前，它会产生一个错误：
Unknown column 't1.b' in 'where clause'
优化器以一种不需要将派生表实例化的方式确定有关派生表的信息，因此EXPLAIN不需要将其实例化。请参见 Section 10.2.2.4，“使用合并或实例化优化派生表、视图引用和通用表达式”。
在某些情况下，使用EXPLAIN SELECT可能会修改表数据。如果外部查询访问任何表，并且内部查询调用修改表中一个或多个行的存储函数，则可能会发生这种情况。假设数据库d1中有两个表t1和t2，以及一个修改t2的存储函数f1，创建如下所示：
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE FUNCTION f1(p1 INT) RETURNS INT
BEGIN
INSERT INTO t2 VALUES (p1);
RETURN p1;
END;
直接在EXPLAIN SELECT中引用函数对t2没有影响，如下所示：
mysql> SELECT * FROM t2;
Empty set (0.02 sec)
mysql> EXPLAIN SELECT f1(5)\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used 1 row in set (0.01 sec)
mysql> SELECT * FROM t2;
Empty set (0.01 sec)
这是因为SELECT语句没有引用任何表，可以在输出的table和Extra列中看到。这也适用于以下嵌套的SELECT：
mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1\. row ***************************
id: 1
select_type: PRIMARY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used 1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)
但是，如果外部SELECT引用任何表，优化器也会执行子查询中的语句，结果导致t2被修改：
mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G
*************************** 1\. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2\. row ***************************
id: 1
select_type: PRIMARY
table: a1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3\. row ***************************
id: 2
select_type: DERIVED
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used 3 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+
| c1 |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
衍生表优化也可以与许多相关的（标量）子查询一起使用（MySQL 8.0.24 及更高版本）。有关更多信息和示例，请参见第 15.2.15.7 节，“相关子查询”。
