SQL in a Nutshell, 2nd Edition

Name

ALL/ANY/SOME Operators

Synopsis

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, performs a Boolean test of a subquery for the existence of a value in any of the rows tested.

Platform	Command
DB2	Supported
MySQL	Not 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, for ANY and SOME, to return a Boolean TRUE value.

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 happen: 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 records in the set do not match the value comparison. 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 ...