子查询

子查询简介

子查询是出现在另一个查询语句中的查询。子查询也称为子 SELECT 或嵌套 SELECT。整个 SELECT 语法在子查询中都有效。

表达式子查询

只要表达式有效，便可在查询中使用表达式子查询。它们返回单个值，而不是列或表。表达式子查询可以相关。

标量子查询

( subquery )

说明

表达式中的子查询被解释为标量子查询。标量子查询常用于 SELECT 列表或 WHERE 子句中。

标量子查询必须选择单个列。试图选择多个列会导致分析错误。单个表达式的 SELECT 列表是选择单个列的最简单方法。标量子查询的结果类型是该表达式的类型。

另一种可能是使用 SELECT AS STRUCT 定义选择单个 STRUCT 类型值的子查询，该值的字段由一个或多个表达式定义。

如果子查询只返回一行，则该单一值就是标量子查询的结果。如果子查询未返回任何行，则结果为 NULL。如果子查询返回多个行，则查询会失败并返回运行时错误。

示例

在此示例中，相关的标量子查询使用 Players 和 Mascots 表针对玩家列表返回吉祥物：

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
  Players;

+---------------------------+
| username  | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

在此示例中，聚合标量子查询计算 avg_level，即 Players 表中用户的平均水平。

SELECT
  username,
  level,
  (SELECT AVG(level) FROM Players) AS avg_level
FROM
  Players;

+---------------------------------------+
| username  | level      | avg_level    |
+---------------------------------------+
| gorbie    | 29         | 24.66        |
| junelyn   | 2          | 24.66        |
| corba     | 43         | 24.66        |
+---------------------------------------+

ARRAY 子查询

ARRAY ( subquery )

说明

ARRAY 子查询是表达式子查询的一种特殊情况，因为它会返回 ARRAY。如果子查询未返回任何行，则会返回一个空的 ARRAY。永远不会返回 NULL ARRAY。

ARRAY 子查询中的 SELECT 列表只能有一个类型的列，用于定义数组子查询返回的数组元素类型。否则，将返回错误。使用 SELECT AS STRUCT 编写子查询时，SELECT 列表可以包含多个列，并且数组子查询返回的值是构造的 STRUCT 的 ARRAY。如果在不使用 SELECT AS 的情况下选择多个列，则会发生错误。

ARRAY 子查询可以使用 SELECT AS STRUCT 来构建结构体数组。

如需了解完整的语义，请参阅数组函数。

示例

在此示例中，ARRAY 子查询会返回 NPCs 表中分配给红队的一组用户名：

SELECT
  ARRAY(SELECT username FROM NPCs WHERE team = 'red') AS red;

+-----------------+
| red             |
+-----------------+
| [niles,jujul]   |
+-----------------+

IN 子查询

value [ NOT ] IN ( subquery )

说明

如果 value 位于子查询返回的一组行中，则返回 TRUE。如果子查询未返回任何行，则返回 FALSE。

子查询的 SELECT 列表必须具有任何类型的单列，且其类型必须与 value 的类型相当。否则，将返回错误。如需了解完整的语义（包括 NULL 处理），请参阅 IN 运算符。

如果您需要将 IN 子查询与数组一起使用，则以下项等效：

value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )

示例

在此示例中，IN 运算符用于检查 Players 表中是否存在名为 corba 的用户名：

SELECT
  'corba' IN (SELECT username FROM Players) AS result;

+--------+
| result |
+--------+
| TRUE   |
+--------+

EXISTS 子查询

EXISTS( subquery )

说明

如果子查询生成一行或多行，则返回 TRUE。如果子查询未生成任何行，则返回 FALSE。永远不会返回 NULL。与其他所有表达式子查询不同，该查询不存在有关列列表的规则。可选用任意数量的列，这不影响查询结果。

示例

在此示例中，EXISTS 运算符使用 Players 表检查是否生成了行：

SELECT
  EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

表子查询

FROM ( subquery ) [ [ AS ] alias ]

说明

使用表子查询，外部查询将子查询的结果视为表。这些运算符只能在 FROM 子句中使用。

示例

在此示例中，子查询会从 Players 表返回一个用户名表：

SELECT results.username
FROM (SELECT * FROM Players) AS results;

+-----------+
| username  |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

在此示例中，系统会返回分配给红队的 NPCs 列表。

SELECT
  username
FROM (
  WITH red_team AS (SELECT * FROM NPCs WHERE team = 'red')
  SELECT * FROM red_team
);

+-----------+
| username  |
+-----------+
| niles     |
| jujul     |
+-----------+

易失性子查询

易失性子查询是一种子查询，其并不总是在相同的输入上产生相同的结果。例如，如果子查询包含返回随机数的函数，则该子查询是易失性的，因为结果并非始终相同。

示例

在此示例中，系统会从 Players 表返回随机数量的用户名。

SELECT
  results.username
FROM
  (SELECT * FROM Players WHERE RAND() < 0.5) AS results;

-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+----------+
| username |
+----------+
| gorbie   |
| junelyn  |
+----------+

子查询的评估规则

某些子查询会进行一次评估，有些子查询的评估频率会较高。

非相关的易失性子查询可能会每行重新评估一次，具体取决于您的查询计划。
必须为每个不同的参数值集在逻辑上重新评估关联子查询。根据您的查询计划，即使多个行具有相同的参数值，相关的子查询也可能每行重新评估一次。

示例中使用的通用表

一些示例引用名为 Players 的表：

+-----------------------------+
| username  | level   | team  |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

一些示例引用名为 NPCs 的表：

+-------------------+
| username  | team  |
+-------------------+
| niles     | red   |
| jujul     | red   |
| effren    | blue  |
+-------------------+

一些示例引用名为 Mascots 的表：

+-------------------+
| mascot   | team   |
+-------------------+
| cardinal | red    |
| parrot   | green  |
| finch    | blue   |
| sparrow  | yellow |
+-------------------+

您可以使用此 WITH 子句来模拟支持 WITH 子句的子查询中的 Players 和 NPCs 的临时表名：

WITH
  Players AS (
    SELECT 'gorbie' AS username, 29 AS level, 'red' AS team UNION ALL
    SELECT 'junelyn', 2 , 'blue' UNION ALL
    SELECT 'corba', 43, 'green'),
  NPCs AS (
    SELECT 'niles' AS username, 'red' AS team UNION ALL
    SELECT 'jujul', 'red' UNION ALL
    SELECT 'effren', 'blue'),
  Mascots AS (
    SELECT 'cardinal' AS mascot , 'red' AS team UNION ALL
    SELECT 'parrot', 'green' UNION ALL
    SELECT 'finch', 'blue' UNION ALL
    SELECT 'sparrow', 'yellow')
SELECT * FROM (
  SELECT username, team FROM Players UNION ALL
  SELECT username, team FROM NPCs);

子查询