子查询简介
子查询是出现在另一个查询语句中的查询。子查询也称为子 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 |
+-----------+
相关子查询
相关子查询是从该子查询之外引用列的子查询。相关性可防止重用子查询结果。您可以在此了解相关详情。
示例
在此示例中,系统将返回未分配任何玩家的吉祥物列表。引用了 Mascots
和 Players
表。
SELECT mascot
FROM Mascots
WHERE
NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);
+----------+
| mascot |
+----------+
| sparrow |
+----------+
在此示例中,相关的标量子查询使用 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 |
+---------------------------+
易失性子查询
易失性子查询是一种子查询,其并不总是在相同的输入上产生相同的结果。例如,如果子查询包含返回随机数的函数,则该子查询是易失性的,因为结果并非始终相同。
示例
在此示例中,系统会从 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);