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 |