# Subquery Operators¶

This topic provides reference information about the subquery operators supported in Snowflake. A subquery is a query within another query.

In this Topic:

## 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.

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