Subquery OperatorsΒΆ
This topic provides reference information about the subquery operators supported in Snowflake. A subquery is a query within another query.
ALL / ANYΒΆ
The ALL and ANY keywords can be used to apply a comparison operator to the values produced by a subquery (which can return more than one row).
SyntaxΒΆ
<expr> comparisonOperator { ALL | ANY} ( <query> )
Where:
comparisonOperator ::= { = | != | > | >= | < | <= }
Usage NotesΒΆ
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, otherwise it returns FALSE.
If ALL is specified, then the result is TRUE if every row of the subquery satisfies the condition, otherwise it returns FALSE.
ANY/ALL subqueries are currently supported only in a WHERE clause.
ANY/ALL subqueries 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);
[ 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:
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 a WHERE clause.
Correlated EXISTS subqueries 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);
[ NOT ] INΒΆ
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 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 do not involve a subquery. For details, see [ NOT ] IN.
ExamplesΒΆ
Use a NOT IN subquery that is equivalent to the != ALL
subquery example (earlier in this topic):
SELECT department_id FROM departments d WHERE d.department_id NOT IN (SELECT e.department_id FROM employees e);