标准 SQL 中的子查询

子查询简介

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

表达式子查询

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

标量子查询

( subquery )

说明

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

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

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

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

示例

在此示例中,相关的标量子查询使用 PlayersGuilds 表针对玩家列表返回吉祥物:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

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

在本例中,聚合标量子查询计算 avg_level,即 Players 表中用户帐号的平均级别。

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

+---------------------------------------+
| account   | 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 account FROM NPCs WHERE guild = 'red') as red
FROM NPCs LIMIT 1;

+-----------------+
| 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 account FROM Players) as result;

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

EXISTS 子查询

EXISTS ( subquery )

说明

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

示例

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

SELECT EXISTS(SELECT account FROM Players WHERE guild="yellow") as result;

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

表子查询

FROM ( subquery ) [ [ AS ] alias ]

说明

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

示例

在此示例中,子查询从 Players 表返回一个帐号表:

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

+-----------+
| account   |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

在此示例中,系统会返回分配给红色公会的 NPCs 列表。

SELECT account FROM (
  WITH red_guild AS (SELECT * FROM NPCs WHERE guild='red')
  SELECT * FROM red_guild);

+-----------+
| account   |
+-----------+
| niles     |
| jujul     |
+-----------+

相关子查询

相关子查询是从该子查询之外引用列的子查询。相关性可防止重用子查询结果。您可以在此了解相关详情。

示例

在此示例中,系统将返回未分配任何玩家的吉祥物列表。引用了 GuildsPlayers 表。

SELECT mascot
FROM Guilds
WHERE NOT EXISTS (SELECT account
  FROM Players
  WHERE Guilds.id = Players.guild)

+----------+
| mascot   |
+----------+
| sparrow  |
+----------+

在此示例中,相关的标量子查询使用 PlayersGuilds 表针对玩家列表返回吉祥物:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

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

易失性子查询

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

示例

在本例中,从 Players 表中返回随机数量的帐号。

SELECT results.account
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:
+---------+
| account |
+---------+
| gorbie  |
| junelyn |
+---------+

子查询的评估规则

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

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

示例中使用的通用表

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

+-----------------------------+
| account   | level   | guild |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

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

+-------------------+
| account   | guild |
+-------------------+
| niles     | red   |
| jujul     | red   |
| effren    | blue  |
+-------------------+

一些示例引用名为 Guilds 的表:

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

您可以使用此 WITH 子句来模拟支持 WITH 子句的子查询中的 PlayersNPCs 的临时表名:

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