ANY / ALL / SOME Quantifiers
Purpose
Enables quantification in a comparison operation or IN/NOT IN predicate.
Syntax
where:
|
Syntax element … |
Specifies … |
|
expression |
an expression that specifies a value. |
|
comparison_operator |
a comparison operator that compares the expression or list of expressions and the literals in the list (Literals syntax) or the subquery (Subquery syntax) to produce a TRUE, FALSE or UNKNOWN result. For more information on comparison operators, see Chapter 10: “Comparison Operators and Functions.” |
|
[NOT] IN |
a predicate that tests the existence of the expression or list of expressions in the list of literals (Literals syntax) or the subquery (Subquery syntax) to produce a TRUE, FALSE, or UNKNOWN result. For more information on IN/NOT IN, see “IN/NOT IN” on page 943. |
|
literal |
a literal value. |
|
subquery |
a subquery that selects the same number of expressions as are specified in the expression or list of expressions. The subquery cannot specify a SELECT AND CONSUME statement. |
ANSI Compliance
ANY, SOME, and ALL are ANSI SQL:2011 compliant quantifiers.
ANY/ALL/SOME Quantifiers and Literal Syntax
When a list of literals is used with quantifiers and comparison operations or IN/NOT IN predicates, the results are determined as follows.
|
IF the predicate is … |
AND specifies … |
THEN the result is true when … |
|
a comparison operation
|
ALL |
the comparison of expression and every literal in the list produces true results. |
|
ANY |
the comparison of expression and any literal in the list is true. |
|
|
SOME |
||
|
IN
|
ALL |
expression is equal to every literal in the list. |
|
ANY |
expression is equal to any literal in the list. |
|
|
SOME |
||
|
NOT IN
|
ALL |
expression is not equal to any literal in the list. |
|
ANY |
expression is not equal to every literal in the list. |
|
|
SOME |
For comparison operations, implicit conversion rules are the same as for the comparison operators.
If expression evaluates to NULL, the result is considered to be unknown.
ANY/ALL/SOME Quantifiers and Subquery Syntax
When subqueries are used with quantifiers and comparison operations or IN/NOT IN predicates, the results are determined as follows.
|
IF this quantifier is specified … |
AND the predicate is … |
THEN the result is … |
WHEN … |
|
ALL
|
a comparison operation |
TRUE
|
the comparison of expression and every value in the set of values returned by subquery produces true results. |
|
IN |
TRUE |
expression is equal to every value in the set of values returned by subquery. |
|
|
NOT IN |
TRUE |
expression is not equal to any value in the set of values returned by subquery. |
|
|
ALL
|
a comparison operation |
TRUE
|
subquery returns no values.
|
|
IN |
|||
|
NOT IN |
|||
|
ANY SOME
|
a comparison operation |
TRUE |
the comparison of expression and at least one value in the set of values returned by subquery is true. |
|
IN |
TRUE |
expression is equal to at least one value in the set of values returned by subquery. |
|
|
NOT IN |
TRUE |
expression is not equal to at least one value in the set of values returned by subquery. |
|
|
a comparison operation |
FALSE
|
subquery returns no values.
|
|
|
IN |
|||
|
NOT IN |
Equivalences Using ANY/ALL/SOME and Comparison Operators
The following table provides equivalences for the ANY/ALL/SOME quantifiers, where op is a comparison operator:
|
This … |
Is equivalent to … |
x op ALL (:a, :b, :c)
|
(x op :a) AND (x op :b) AND (x op :c)
|
x op ANY (:a, :b, :c)
|
(x op :a) OR (x op :b) OR (x op :c)
|
x op SOME (:a, :b, :c)
|
Here are some examples:
|
This expression … |
Is equivalent to … |
x < ALL (:a, :b, :c)
|
(x < :a) AND (x < :b) AND (x < :c)
|
x > ANY (:a, :b, :c)
|
(x > :a) OR (x > :b) OR (x > :c)
|
x > SOME (:a, :b, :c)
|
Equivalences Using ANY/ALL/SOME and IN/NOT IN
The following table provides equivalences for the ANY/ALL/SOME quantifiers, where op is IN or NOT IN:
|
This … |
Is equivalent to …1 |
NOT (x op ALL (:a, :b, :c))
|
x NOT op ANY (:a, :b, :c)
|
x NOT op SOME (:a, :b, :c)
|
|
NOT (x op ANY (:a, :b, :c))
|
x NOT op ALL (:a, :b, :c)
|
NOT (x op SOME (:a, :b, :c))
|
Here are some examples:
|
This expression … |
Is equivalent to … |
NOT (x IN ANY (:a, :b, :c))
|
x NOT IN ALL (:a, :b, :c)
|
NOT (x IN ALL (:a, :b, :c))
|
x NOT IN ANY (:a, :b, :c)
|
NOT (x NOT IN ANY (:a, :b, :c))
|
x IN ALL (:a, :b, :c)
|
NOT (x NOT IN ALL (:a, :b, :c))
|
x IN ANY (:a, :b, :c)
|
Example
The following statement uses a comparison operator with the ANY quantifier to select the employee number, name, and department number of anyone in departments 100, 300, and 500:
|
This Expression … |
Is Equivalent to this expression… |
SELECT EmpNo, Name, DeptNo
|
SELECT EmpNo, Name, DeptNo
and SELECT EmpNo, Name, DeptNo
|
Example
Here is an example that uses a subquery in a comparison operation that specifies the ALL quantifier:
SELECT EmpNo, Name, JobTitle, Salary, YrsExp
FROM Employee
WHERE (Salary, YrsExp) >= ALL
(SELECT Salary, YrsExp FROM Employee) ;
Example
This example shows the behavior of ANY/ALL/SOME.
Consider the following table definition and contents:
CREATE TABLE t (x INTEGER);
INSERT t (1);
INSERT t (2);
INSERT t (3);
INSERT t (4);
INSERT t (5);
|
IF you use this query … |
THEN the result is … |
SELECT * FROM t WHERE x IN ANY (1,2)
|
1, 2
|
SELECT * FROM t WHERE x = SOME (1,2)
|
1, 2
|
SELECT * FROM t WHERE x NOT IN ALL (1,2)
|
3, 4, 5
|
SELECT * FROM t WHERE NOT (x IN ANY (1,2))
|
3, 4, 5
|
SELECT * FROM t WHERE NOT (x = SOME (1,2))
|
3, 4, 5
|
SELECT * FROM t WHERE x NOT IN SOME (1, 2)
|
1, 2, 3, 4, 5
|
SELECT * FROM t WHERE x NOT = ANY (1, 2)
|
1, 2, 3, 4, 5
|
SELECT * FROM t WHERE x IN ALL (1,2)
|
no rows |
SELECT * FROM t WHERE NOT (x NOT IN SOME (1,2))
|
no rows |
SELECT * FROM t WHERE x = ALL (1,2)
|
no rows |
SELECT * FROM t WHERE NOT (x NOT = ANY (1,2))
|
no rows |