子查询

子查询（也称为内部查询或嵌套查询）是查询中的查询。子查询可以用在 SELECT、FROM、WHERE 和 HAVING 子句中。

以下示例基于以下表。

SELECT * FROM x;

+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+
| 2        | 4        |
+----------+----------+

SELECT * FROM y;

+--------+--------+
| number | string |
+--------+--------+
| 1      | one    |
+--------+--------+
| 2      | two    |
+--------+--------+
| 3      | three  |
+--------+--------+
| 4      | four   |
+--------+--------+

子查询操作符

[ NOT ] EXISTS

EXISTS 操作符返回所有满足以下条件的行：关联子查询针对该行产生一个或多个匹配结果。NOT EXISTS 返回所有满足以下条件的行：关联子查询针对该行产生的匹配结果为零。仅支持关联子查询。

[NOT] EXISTS (subquery)

[ NOT ] IN

IN 运算符会返回所有这样的行：给定表达式的值能够在关联子查询的结果中找到。NOT IN 则返回所有这样的行：给定表达式的值无法在子查询或值列表的结果中找到。

expression [NOT] IN (subquery|list-literal)

示例

SELECT * FROM x WHERE column_1 IN (1,3);

+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+

SELECT * FROM x WHERE column_1 NOT IN (1,3);

+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 2        | 4        |
+----------+----------+

元组类值与 `NULL` 结合使用的 `IN`

对于元组类的值，IN 采用 DataFusion 的结构体相等语义：

SELECT (1, 1) IN ((1, NULL));
-- false

SELECT (1, NULL) IN ((1, NULL));
-- true

SELECT 子句子查询

SELECT 子句子查询会将内部查询返回的值用作外部查询 SELECT 列表的一部分。SELECT 子句仅支持标量子查询，即每次执行内部查询只返回单个值。该返回值可以按行各不相同。

SELECT [expression1[, expression2, ..., expressionN],] (<subquery>)

注意：SELECT 子句中的子查询可作为 JOIN 操作的替代方案。

示例

SELECT
  column_1,
  (
    SELECT
      first_value(string)
    FROM
      y
    WHERE
      number = x.column_1
  ) AS "numeric string"
FROM
  x;

+----------+----------------+
| column_1 | numeric string |
+----------+----------------+
|        1 | one            |
|        2 | two            |
+----------+----------------+

FROM 子句子查询

FROM 子句子查询会返回一组结果，随后由外层查询对这些结果进行查询和处理。

SELECT expression1[, expression2, ..., expressionN] FROM (<subquery>)

要在同一 FROM 子句中引用其他表的列，请使用 LATERAL JOIN。

示例

下面的查询返回每个房间的最大值的平均值。内层查询返回每个房间中各个字段的最大值。外层查询使用内层查询的结果，并返回每个字段的最大值的平均值。

SELECT
  column_2
FROM
  (
    SELECT
      *
    FROM
      x
    WHERE
      column_1 > 1
  );

+----------+
| column_2 |
+----------+
|        4 |
+----------+

WHERE 子句子查询

WHERE 子句子查询会将某个表达式与子查询的结果进行比较，并返回 true 或 false。求值结果为 false 或 NULL 的行会从结果中被过滤掉。WHERE 子句既支持关联子查询和非关联子查询，也支持标量子查询和非标量子查询（具体取决于谓词表达式中使用的运算符）。

SELECT
  expression1[, expression2, ..., expressionN]
FROM
  <measurement>
WHERE
  expression operator (<subquery>)

注意： WHERE 子句子查询可以作为 JOIN 操作的替代方案。

示例

带有标量子查询的 `WHERE` 子句

以下查询返回 column_2 值高于 y 中所有 number 值平均值的所有行。

SELECT
  *
FROM
  x
WHERE
  column_2 > (
    SELECT
      AVG(number)
    FROM
      y
  );

+----------+----------+
| column_1 | column_2 |
+----------+----------+
|        2 |        4 |
+----------+----------+

带有非标量子查询的 `WHERE` 子句

非标量子查询必须使用 [NOT] IN 或 [NOT] EXISTS 运算符，并且只能返回单列。返回列中的值将作为列表进行评估。

以下查询返回表 x 中 column_2 的值属于表 y 中字符串长度大于三的数字列表的所有行。

SELECT
  *
FROM
  x
WHERE
  column_2 IN (
    SELECT
      number
    FROM
      y
    WHERE
      length(string) > 3
  );

+----------+----------+
| column_1 | column_2 |
+----------+----------+
|        2 |        4 |
+----------+----------+

`WHERE` 子句中的关联子查询

以下查询返回表 x 中 column_2 值大于表 y 中 string 值平均长度的行。WHERE 子句中的子查询使用外层查询的 column_1 值，返回该特定值对应的 string 值平均长度。

SELECT
  *
FROM
  x
WHERE
  column_2 > (
    SELECT
      AVG(length(string))
    FROM
      y
    WHERE
      number = x.column_1
  );

+----------+----------+
| column_1 | column_2 |
+----------+----------+
|        2 |        4 |
+----------+----------+

HAVING 子句子查询

HAVING 子句子查询会将 SELECT 子句中聚合函数返回的聚合值所构成的表达式与子查询的结果进行比较，并返回 true 或 false。求值结果为 false 的行会从结果中被过滤掉。HAVING 子句支持相关子查询和非相关子查询，也支持标量子查询和非标量子查询（取决于谓词表达式中使用的运算符）。

SELECT
  aggregate_expression1[, aggregate_expression2, ..., aggregate_expressionN]
FROM
  <measurement>
WHERE
  <conditional_expression>
GROUP BY
  column_expression1[, column_expression2, ..., column_expressionN]
HAVING
  expression operator (<subquery>)

示例

以下查询计算表 y 中偶数和奇数的平均值，并返回等于表 x 中 column_1 最大值的平均值。

带标量子查询的 `HAVING` 子句

SELECT
  AVG(number) AS avg,
  (number % 2 = 0) AS even
FROM
  y
GROUP BY
  even
HAVING
  avg = (
    SELECT
      MAX(column_1)
    FROM
      x
  );

+-------+--------+
|   avg | even   |
+-------+--------+
|     2 | false  |
+-------+--------+

带有非标量子查询的 `HAVING` 子句

非标量子查询必须使用 [NOT] IN 或 [NOT] EXISTS 运算符，并且只能返回单个列。返回列中的值会被作为列表进行评估。

下面的查询计算表 y 中偶数和奇数的平均值，并返回位于表 x 的 column_1 中的那些平均值。

SELECT
  AVG(number) AS avg,
  (number % 2 = 0) AS even
FROM
  y
GROUP BY
  even
HAVING
  avg IN (
    SELECT
      column_1
    FROM
      x
  );

+-------+--------+
|   avg | even   |
+-------+--------+
|     2 | false  |
+-------+--------+

子查询分类

根据子查询的行为，子查询可以归入以下一类或多类：

非相关子查询

在非相关子查询中，内层查询不依赖于外层查询，而是独立执行。内层查询先执行，然后将结果传递给外层查询。

标量子查询

标量子查询返回单个值（一行一列）。如果没有返回任何行，子查询返回 NULL。

非标量子查询

非标量子查询返回 0 行、1 行或多行，每行可能包含 1 列或多列。对于每一列，如果没有可返回的值，子查询返回 NULL。如果没有符合条件的行需要返回，子查询返回 0 行。

子查询

子查询