Subquery Operators

Subquery operators operate on nested query expressions. They can be used to compute values that are:

Subqueries can be categorized as correlated or uncorrelated:

  • A correlated subquery refers to columns from outside of the subquery. A correlated subquery can be thought of as a filter on the table that it refers to, as if the subquery were evaluated on each row of the table.
  • An uncorrelated subquery has no such column references.

Snowflake currently supports the following types of subqueries:

  • Uncorrelated scalar subqueries.
  • EXISTS, ANY, ALL, and IN subqueries in the WHERE clause.

In this Topic:

Scalar Subqueries

A scalar subquery is a subquery that returns at most one row. A scalar subquery can appear anywhere that a value expression can appear, including the SELECT list, GROUP BY clause, or as an argument to a function in the WHERE or HAVING clauses.

Syntax

( <query> )

Usage Notes

  • A scalar subquery can contain only one item in the SELECT list.
  • If a scalar subquery returns more than one row, a runtime error is generated.
  • Correlated scalar subqueries are currently supported only if they can be statically determined to return one row, for example if the SELECT list contains an aggregate function with no GROUP BY.
  • Uncorrelated scalar subqueries are supported anywhere that a value expression is allowed.
  • Subqueries with a correlation inside of FLATTEN are currently unsupported.

Examples

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

EXISTS / NOT EXISTS Subqueries

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

  • An EXISTS expression evaluates to TRUE if any rows are produced by the subquery.
  • A NOT EXISTS expression evaluates to TRUE if no rows are produced by the subquery.

Syntax

[ NOT ] EXISTS ( <query> )

Usage Notes

  • Correlated EXISTS subqueries are currently supported only in the WHERE clause, and cannot appear as an argument to an OR operator.
  • Uncorrelated EXISTS subqueries are supported anywhere that a boolean expression is allowed.

Examples

Use a correlated NOT EXISTS subquery to find the departments that have no employees:

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

ANY / ALL Subqueries

The ANY and ALL keywords can be used to apply a comparison operator to the values produced by a subquery (which may return more than one row).

Syntax

<expr> { = | != | < | <= | > | >= } { ANY | ALL } ( <query> )

The expression is compared with the operator to each value that the subquery returns. If ANY is specified, then the result is TRUE if any row of the subquery satisfies the condition, and FALSE otherwise. If ALL is specified, then the result is TRUE if every row of the subquery satisfies the condition, and FALSE otherwise.

Usage Notes

  • ANY/ALL subqueries are currently supported only in the WHERE clause, and cannot appear as an argument to an OR operator.
  • The subquery must contain only one item in its SELECT list.

Examples

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);

IN / NOT IN Subqueries

The IN and NOT IN operators check if an expression is included or not included in the values returned by a subquery.

Syntax

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

Usage Notes

  • IN is shorthand for =ANY, and is subject to the restrictions on ANY subqueries.
  • NOT IN is shorthand for !=ALL, and is subject to the restrictions on ALL subqueries.

Examples

Use a NOT IN subquery that is equivalent to the !=ALL subquery in the previous example:

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