使用子查询

子查询是另一个查询中的一个查询。FROM 或 WHERE 子查询用于提供数据，这些数据将用于限制或比较/评估包含查询返回的数据。

本主题内容：

子查询的类型

相关子查询与非相关子查询

子查询可以分为 相关 子查询或 非相关 子查询：

  • 相关子查询是指子查询外部的一列或多列。（这些列通常在子查询的子句中 WHERE 引用。）可以将相关子查询视为它所引用表上的筛选器，就好像在外部查询中对表的每一行都进行了子查询评估一样。

  • 非相关子查询没有此类外部列引用。它是一个独立的查询，其结果返回给外部查询并由外部查询使用一次（而不是每一行）。

例如：

-- Uncorrelated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT MAX(x) FROM table2);

-- Correlated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT x FROM table2 WHERE y = table1.c2);
Copy

标量与非标量子查询

子查询也可分为 标量 子查询或 非标量 子查询：

  • 标量子查询会返回单个值（一行一列）。如果没有符合返回条件的行，则子查询将返回 NULL。

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

Snowflake 支持的类型

Snowflake 目前支持以下类型的子查询：

  • 可以使用值表达式的任何位置的非相关标量子查询。

  • WHERE 子句中的相关标量子查询。

  • WHERE 子句中的 EXISTS、 ANY / ALL 和 IN 子查询。这些子查询可以是相关的，也可以是非相关的。

子查询运算符

借助 子查询运算符，可对嵌套查询表达式进行操作。它们可用于计算以下值：

  • 在 SELECT 列表中返回的值。

  • 在 GROUP BY 子句中分组的值。

  • 与 WHERE 或 HAVING 子句中的其他表达式进行比较的值。

相关子查询和非相关子查询之间的差异

以下查询演示了 WHERE 子句中的非相关子查询。子查询获取巴西的人均 GDP，外部查询选择薪酬低于巴西人均 GDP 的所有工作（在任何国家/地区中）。该子查询是非相关子查询，因为它返回的值不取决于外部查询的任何列。在外部查询的整个执行过程中，子查询只需调用一次。

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT per_capita_GDP
                           FROM international_GDP
                           WHERE name = 'Brazil');
Copy

下一个查询演示 WHERE 子句中的相关子查询。该查询列出了该工作年薪低于该国家/地区人均 GDP 的工作。该子查询是相关子查询，因为它对外部查询中的每一行都进行了一次调用，并从该行传递值 p.country （国家/地区名称）。

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT MAX(per_capita_GDP)
                           FROM international_GDP i
                           WHERE p.country = i.name);
Copy

备注

在这种情况下，MAX 聚合函数在逻辑上不是必需的，因为 international_GDP 表中每个国家/地区只有一行；但是，由于服务器不知道这一点，并且由于服务器要求子查询返回的行数不得超过一行，因此查询使用聚合函数强制服务器识别子查询在每次执行子查询时仅返回一行。

函数 MIN 和 AVG 也可发挥作用，因为将其中任何一个应用于单个值都会使该值保持不变。

标量子查询

标量子查询是最多返回一行的子查询。标量子查询可以出现在值表达式可以出现的任何位置，包括 SELECT 列表、 GROUP BY 子句，或者作为 WHERE 或 HAVING 子句中函数的实参。

使用说明

  • 标量子查询在 SELECT 列表中只能包含一个项目。

  • 如果标量子查询返回多行，则会生成运行时错误。

  • 当前仅当相关标量子查询可以静态确定为返回一行时（例如，如果 SELECT 列表包含不带 GROUP BY 的聚合函数），相关标量子查询才能得到支持。

  • 任何允许值表达式的地方都支持非相关标量子查询。

  • 目前不支持在 FLATTEN 中具有相关性的子查询。

  • LIMIT / FETCH 子句只允许在非相关标量子查询中使用。

示例

此示例显示了 WHERE 子句中的基本非相关子查询：

SELECT employee_id
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);
Copy

此示例显示了 FROM 子句中的非相关子查询；此基本子查询返回 international_GDP 表中信息的子集。总体查询列出了“高工资”国家/地区的工作，这些国家/地区的年薪与该国家/地区的 per_capita_GDP 相同。

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p INNER JOIN (SELECT name, per_capita_GDP
                              FROM international_GDP
                              WHERE per_capita_GDP >= 10000.0) AS pcg
    ON pcg.per_capita_GDP = p.annual_wage AND p.country = pcg.name;
Copy

限制

尽管子查询可以包含各种 SELECT 语句，但它们存在以下限制：

  • 某些子句不允许在 ANY/ALL/NOT EXISTS 子查询中使用。

  • 唯一允许 LIMIT / FETCH 子句的子查询类型是非相关标量子查询。此外，由于非相关标量子查询仅会返回 1 行，因此 LIMIT 子句在子查询中几乎没有实际价值。

语言: 中文