ALL(), ANY() and SOME()
The ALL, ANY and SOME quantifiers can be used
together with comparison operators in the following way.
| Expression | Meaning |
|---|---|
|
A = ALL(...) |
Evaluates to true when A is equal to all values. |
|
A <> ALL(...) |
Evaluates to true when A does not match any value. |
|
A < ALL(...) |
Evaluates to true when A is smaller than the smallest value. |
|
A = ANY(...) |
Evaluates to true when A is equal to any of the values. |
|
A <> ANY(...) |
Evaluates to true when A does not match one or more values. |
|
A < ANY(...) |
Evaluates to true when A is smaller than the biggest value. |
Examples and usage
notes
Note
When using ALL, ANY or SOME, the keyword
VALUES should be used if the comparison values are a list of literals.
Example: ANY()
An example of ANY() in a query statement as follows.
SELECT 11.7 = ANY (VALUES 12.0, 13.5, 11.7)An alternative syntax for the same operation is as follows.
SELECT 11.7 = ANY (SELECT 12.0 UNION ALL SELECT 13.5 UNION ALL SELECT 11.7)In this case, ANY() evaluates to True.
Example: ALL()
An example of ALL() in a query statement as follows.
SELECT 17 < ALL (VALUES 19, 20, 15);An alternative syntax for the same operation is as follows.
SELECT 17 < ALL (SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 15);In this case, ALL() evaluates to False.
Example: SOME()
An example of SOME() in a query statement as follows.
SELECT 50 >= SOME (VALUES 53, 77, 27);An alternative syntax for the same operation is as follows.
SELECT 50 >= SOME (SELECT 53 UNION ALL SELECT 77 UNION ALL SELECT 27);In this case, SOME() evaluates to True.
