In its simplest form, a subquery is a scalar subquery that returns a single value. A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication that it can be NULL
, and so on. For example:
在其最简单的形式中,子查询是返回单个值的标量子查询。标量子查询是一个简单的操作数,你几乎可以在任何地方使用它一个列值或文字是合法的,你可以期望它有这些特征,所有操作数:一个数据类型,长度,表明它可以为空,等等。例如:
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);
The subquery in this SELECT returns a single value ('abcde'
) that has a data type of CHAR, a length of 5, a character set and collation equal to the defaults in effect at CREATE TABLE time, and an indication that the value in the column can be NULL
. Nullability of the value selected by a scalar subquery is not copied because if the subquery result is empty, the result is NULL
. For the subquery just shown, if t1
were empty, the result would be NULL
even though s2
is NOT NULL
.
这个SELECT中的子查询返回一个值('abcde'),该值的数据类型为CHAR,长度为5,字符集和排序规则等于CREATE TABLE时的默认值,并指示列中的值可以为NULL。不复制标量子查询选择的值的空性,因为如果子查询结果为空,则结果为NULL。对于刚才显示的子查询,如果t1为空,结果将是NULL,即使s2不是NULL。
There are a few contexts in which a scalar subquery cannot be used. If a statement permits only a literal value, you cannot use a subquery. For example, LIMIT
requires literal integer arguments, and LOAD DATA requires a literal string file name. You cannot use subqueries to supply these values.
在一些上下文中不能使用标量子查询。如果语句只允许文字值,则不能使用子查询。例如,LIMIT需要文字整型参数,而LOAD DATA需要文字字符串文件名。不能使用子查询来提供这些值。
When you see examples in the following sections that contain the rather spartan construct (SELECT column1 FROM t1)
, imagine that your own code contains much more diverse and complex constructions.
当您在下面的小节中看到包含相当简单的构造(SELECT column1 FROM t1)的示例时,请假设您自己的代码包含更多样化和复杂的构造。
Suppose that we make two tables:
假设我们制作了两个表:
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
Then perform a SELECT:
SELECT (SELECT s1 FROM t2) FROM t1;
The result is 2
because there is a row in t2
containing a column s1
that has a value of 2
.
In MySQL 8.0.19 and later, the preceding query can also be written like this, using TABLE:
结果是2,因为t2中有一行包含值为2的列s1。
在MySQL 8.0.19及更高版本中,前面的查询也可以这样写,使用TABLE:
SELECT (TABLE t2) FROM t1;
A scalar subquery can be part of an expression, but remember the parentheses, even if the subquery is an operand that provides an argument for a function. For example:
标量子查询可以是表达式的一部分,但请记住括号,即使子查询是为函数提供参数的操作数。例如:
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;
The same result can be obtained in MySQL 8.0.19 and later using SELECT UPPER((TABLE t1)) FROM t2
.
在MySQL 8.0.19及以后的版本中使用SELECT UPPER((TABLE t1)) FROM t2也可以得到相同的结果。