Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

Quantified Predicate

Description

The quantified predicate compares a value with a collection of values using a relational operator (see Relational Operators for more detail). A quantified predicate has the same form as a basic predicate with the query_expression being preceded by ALL, ANY, or SOME keyword. The result table returned by query_expression can contain only a single column. A quantified predicate can be used inside of a WHERE, HAVING, or JOIN statement.

When ALL is specified the predicate evaluates to true if the query_expression returns no values or the specified relationship is true for all the values returned.

When SOME or ANY is specified the predicate evaluates to true if the specified relationship is true for at least one value returned by the query_expression. There is no difference between the SOME and ANY keywords. The predicate evaluates to false if the query_expression returns no values or the specified relationship is false for all the values returned.

Syntax

quantified_predicate ::

expr relop { ALL | ANY | SOME } (query_expression)

Example

10 < ANY ( SELECT COUNT(*)

FROM order_tbl

GROUP BY custid )

See also:

Basic Predicate

TOCIndex