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 ... WHEREexpression
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 thesubquery
. Thecomparison
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 ...
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.