Quantified predicate

A quantified predicate compares a value or values with a collection of values.

Notes:

¹ The following forms of the comparison operators are also supported in basic and quantified predicates: ^=, ^<, ^>, !=, !<, and !>. In code pages 437, 819, and 850, the forms ¬=, ¬<, and ¬> are supported. All of these product-specific forms of the comparison operators are intended only to support existing SQL statements that use these operators, and are not recommended for use when writing new SQL statements.

The fullselect must identify a number of columns that is the same as the number of expressions specified to the left of the predicate operator (SQLSTATE 428C4). The fullselect may return any number of rows.

When ALL is specified:

The result of the predicate is true if the fullselect returns no values or if the specified relationship is true for every value returned by the fullselect.
The result is false if the specified relationship is false for at least one value returned by the fullselect.
The result is unknown if the specified relationship is not false for any values returned by the fullselect and at least one comparison is unknown because of the null value.

When SOME or ANY is specified:

The result of the predicate is true if the specified relationship is true for each value of at least one row returned by the fullselect.
The result is false if the fullselect returns no rows or if the specified relationship is false for at least one value of every row returned by the fullselect.
The result is unknown if the specified relationship is not true for any of the rows and at least one comparison is unknown because of a null value.

Examples: Use the following tables when referring to the following examples.

Example tables for quantified predicate — Figure 1. Tables for quantified predicate examples

Example 1

  SELECT COLA FROM TBLAB 
     WHERE COLA = ANY(SELECT COLX FROM TBLXY)

Results in 2,3. The subselect returns (2,3). COLA in rows 2 and 3 equals at least one of these values.

Example 2

  SELECT COLA FROM TBLAB 
     WHERE COLA > ANY(SELECT COLX FROM TBLXY)

Results in 3,4. The subselect returns (2,3). COLA in rows 3 and 4 is greater than at least one of these values.

Example 3

  SELECT COLA FROM TBLAB 
     WHERE COLA > ALL(SELECT COLX FROM TBLXY)

Results in 4. The subselect returns (2,3). COLA in row 4 is the only one that is greater than both these values.

Example 4

  SELECT COLA FROM TBLAB 
     WHERE COLA > ALL(SELECT COLX FROM TBLXY
                         WHERE COLX<0)

Results in 1,2,3,4, null. The subselect returns no values. Thus, the predicate is true for all rows in TBLAB.

Example 5

SELECT * FROM TBLAB 
   WHERE (COLA,COLB+10) = SOME (SELECT COLX, COLY FROM TBLXY)

The subselect returns all entries from TBLXY. The predicate is true for the subselect, hence the result is as follows:

COLA        COLB       
----------- -----------
          2          12
          3          13

Example 6

SELECT * FROM TBLAB 
   WHERE (COLA,COLB) = ANY (SELECT COLX,COLY-10 FROM TBLXY)

The subselect returns COLX and COLY-10 from TBLXY. The predicate is true for the subselect, hence the result is as follows:

COLA        COLB       
----------- -----------
          2          12
          3          13