Show TOC

Quantified comparison predicateLocate this document in the navigation structure

The quantified comparison predicate is used to compare a value with the result of a query.

Syntax 

<quantified comparison predicate> ::= 
    <value expression> <comparison operator> ( ALL | SOME | ANY ) '(' <query specification> ')'.

The <query specification> must have the a result of one column. The data type of the <value expression> must be comparable to the type of that column.

If ALL is specified, the result of the <quantified comparison predicate> is:

  • true , if the result of the <query specification> is empty or if the implied <comparison predicate> between the <value expression> and all the rows of the result of the <query specification> evaluates to true.

  • false , if the implied <comparison predicate> between the <value expression> and at least one row of the result of the <query specification> evaluates to false.

  • unknown , else.

If SOME or ANY is specified, the result of the <quantified comparison predicate> is:

  • true , if the implied <comparison predicate> between the <value expression> and at least one row of the result of the <query specification> evaluates to true.

  • false , if the result of the <query specification> is empty or if the implied <comparison predicate> between the <value expression> and all the rows of the result of the <query specification> evaluates to false.

  • unknown , else.

Example 

SELECT * FROM a WHERE c > ALL ( SELECT c FROM b )

The Quantified Comparison. This SELECT yields all rows from table a where the column a.c has a value that is greater than all values of column b.c in table b .

More Information

Value expressions

Query Specification