SQL in a Nutshell, 3rd Edition

Name

ALL/ANY/SOME Operators

The ALL operator performs a Boolean test of a subquery for the existence of a value in all rows. The ANY operator and its synonym SOME perform a Boolean test of a subquery for the existence of a value in any of the rows tested.

Platform	Command
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

SELECT ...
WHERE expression comparison {ALL | ANY | SOME} ( subquery )

Keywords

WHERE expression

Tests a scalar expression (such as a column) against every value in the subquery for ALL, and against every value until a match is found for ANY and SOME. All rows must match the expression to return a Boolean TRUE value for the ALL operator, while one or more rows must match the expression to return a Boolean TRUE value for the ANY and SOME operators.

comparison

Compares the expression to the subquery. The comparison must be a standard comparison operator like =, <>, !=, >, >=, <, or <=.

Rules at a Glance

The ALL operator returns a Boolean TRUE value when one of two things happens: either the subquery returns an empty set (i.e., no records), or every record in the set meets the comparison. ALL returns FALSE when any record in the set does not match the value comparison. The ANY and SOME operators return a Boolean TRUE when at least one record in the subquery matches the comparison operation, and FALSE when no record matches the comparison operation (or when a subquery returns an empty result set). If even one return value of the subquery is NULL, ...