子查询运算符

A subquery is a query within another query. Subquery operators perform operations on the values produced by subqueries.

Snowflake 支持以下子查询运算符：

• ALL / ANY
• [ NOT ] EXISTS
• [ NOT ] IN

ALL / ANY¶

ALL 和 ANY 关键字可用于将比较运算符应用于子查询生成的值（可以返回多行）。

语法

<expr> comparisonOperator { ALL | ANY } ( <query> )

其中：

comparisonOperator ::=
  { = | != | > | >= | < | <= }

使用说明

• 该表达式通过运算符与子查询返回的每个值进行比较：

  • 如果指定 ALL，则如果子查询的每一行都满足条件，则结果为 TRUE，否则返回 FALSE。
  • 如果指定 ANY，则如果子查询的任意行满足条件，则结果为 TRUE，否则返回 FALSE。

• ANY/ALL subqueries are currently supported only in a WHERE clause.

• ANY/ALL subqueries can’t appear as an argument to an OR operator.

• The subquery must contain only one item in its SELECT list.

示例

Use a != ALL subquery to find the departments that have no employees:

SELECT department_id
  FROM departments d
  WHERE d.department_id != ALL (
    SELECT e.department_id
      FROM employees e);

[ NOT ] EXISTS¶

An EXISTS subquery is a Boolean expression that can appear in a WHERE or HAVING clause, or in any function that operates on a Boolean expression:

• 如果子查询生成了任何行，则 EXISTS 表达式的计算结果是 TRUE。
• 如果子查询未生成任何行，则 NOT EXISTS 表达式的计算结果为 TRUE。

语法

[ NOT ] EXISTS ( <query> )

使用说明

• Correlated EXISTS subqueries are currently supported only in a WHERE clause.
• Correlated EXISTS subqueries cannot appear as an argument to an OR operator.
• 任何允许布尔表达式的地方都支持非相关 EXISTS 子查询。

示例

使用相关 NOT EXISTS 子查询查找没有员工的部门：

SELECT department_id
  FROM departments d
  WHERE NOT EXISTS (
    SELECT 1
      FROM employees e
      WHERE e.department_id = d.department_id);

[ NOT ] IN¶

IN 和 NOT IN 运算符检查表达式是否包含在子查询产生的值中。

语法

<expr> [ NOT ] IN ( <query> )

使用说明

• IN is shorthand for = ANY, and is subject to the same restrictions as ANY subqueries.
• NOT IN is shorthand for != ALL, and is subject to the same restrictions as ALL subqueries.
• [NOT] IN can also be used as an operator in expressions that don’t involve a subquery. For details, see [ NOT ] IN.

示例

Use a NOT IN subquery that is equivalent to the != ALL subquery example (earlier in this topic) to find the departments that have no employees:

SELECT department_id
  FROM departments d
  WHERE d.department_id NOT IN (
    SELECT e.department_id
      FROM employees e);