Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Language Elements » Search conditions

Subqueries in search conditions Next Page

ALL, ANY, and SOME search conditions


ANY search condition

The syntax for an ANY search condition is:

expression comparison-operator ANY ( subquery )

where comparison-operator is one of <=, =, <, >, >=, <>, !<, !>, or !=.

The keyword SOME can be used instead of ANY.

With the ANY search condition, if the subquery result set is the empty set, the search condition evaluates to FALSE. Otherwise, the search condition evaluates to TRUE, FALSE, or UKNOWN, depending on the value of expression, and the result set returned by the subquery, as follows:

If the expression value is..and the result set returned by the subquery contains at least one NULL, then..or the result set returned by the subquery contains no NULLs, then..
NULLUNKNOWNUNKNOWN
not NULLIf there exists at least one value in the subquery result set for which the comparison with the expression value is TRUE, then the search condition evaluates to TRUE. Otherwise, the search condition evaluates to UNKNOWN. If there exists at least one value in the subquery result set for which the comparison with the expression value is TRUE, then the search condition evaluates to TRUE. Otherwise, the search condition evaluates to FALSE.

For example, an ANY search condition with an equality operator,

expression = ANY ( subquery )

evaluates to TRUE if expression is equal to any of the values in the result of the subquery, and FALSE if the value of the expression is not NULL, does not equal any of the values in the result of the subquery, and the result set doesn't contain NULLs.

Note

The usage of =ANY is equivalent to using the IN keyword.

ALL search condition

The syntax for an ALL search condition is:

expression comparison-operator ALL ( subquery )

where comparison-operator is one of <=, =, <, >, >=, <>, !<, !>, or !=.

With the ALL search condition, if the value of subquery result set is the empty set, the search condition evaluates to TRUE. Otherwise, the search condition evaluates to TRUE, FALSE, or UKNOWN, depending on the value of expression, and the result set returned by the subquery, as follows:

If the expression value is..and the result set returned by the subquery contains at least one NULL, then..or the result set returned by the subquery contains no NULLs, then..
NULLUNKNOWNUNKNOWN
not NULLIf there exists at least one value in the subquery result set for which the comparison with the expression value is FALSE, then the search condition evaluates to FALSE. Otherwise, the search condition evaluates to UNKNOWN. If there exists at least one value in the subquery result set for which the comparison with the expression value is FALSE, then the search condition evaluates to FALSE. Otherwise, the search condition evaluates to TRUE.