Quantified predicate

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

Read syntax diagramSkip visual syntax diagramexpression =  <>  <  >  <=  >= 1SOMEANYALL(fullselect)row-value-expression = SOMEANY(fullselect)row-value-expression <> ALL(fullselect)
Notes:
  • 1 Other comparison operators are also supported. 1

When expression is specified, the fullselect must return a single result column. The fullselect can return any number of values, whether null or not null. The result depends on the operator that is specified:

  • When ALL is specified, the result of the predicate is:
    • True if the result of the fullselect is empty, or if the specified relationship is true for every value returned by the fullselect.
    • False if the specified relationship is false for at least one value returned by the fullselect.
    • Unknown if the specified relationship is not false for any values returned by the fullselect and at least one comparison is unknown because of a null value.
  • When SOME or ANY is specified, the result of the predicate is:
    • True if the specified relationship is true for at least one value returned by the fullselect.
    • False if the result of the fullselect is empty, or if the specified relationship is false for every value returned by the fullselect.
    • Unknown if the specified relationship is not true for any of the values returned by the fullselect and at least one comparison is unknown because of a null value.

When row-value-expression is specified, the number of result columns returned by the fullselect must be the same as the number of value expressions specified by row-value-expression. The fullselect can return any number of rows of values. The data types of the corresponding expressions of the row value expressions must be compatible. The value of each expression from row-value-expression is compared with the value of the corresponding result column from the fullselect. SELECT * is not allowed in the outermost select lists of the fullselect. The value of the predicate depends on the operator that is specified:

  • When ALL is specified, the result of the predicate is:
    • True if the result of the fullselect is empty or if the specified relationship is true for every row returned by fullselect.
    • False if the specified relationship is false for at least one row returned by the fullselect.
    • Unknown if the specified relationship is not false for any row returned by the fullselect and at least one comparison is unknown because of a null value.
  • When SOME or ANY is specified, the result of the predicate is:
    • True if the specified relationship is true for at least one row returned by the fullselect.
    • False if the result of the fullselect is empty or if the specified relationship is false for every row returned by the fullselect.
    • Unknown if the specified relationship is not true for any of the rows returned by the fullselect and at least one comparison is unknown because of a null value.

If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the collating sequence.

Examples

Table TBLA 

COLA
-----
    1
    2
    3
    4
 null

Table TBLB 

COLB
-----
    2
    3

Example 1 

  SELECT * FROM TBLA WHERE COLA =  ANY(SELECT COLB FROM TBLB)

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

Example 2 

  SELECT * FROM TBLA WHERE COLA > ANY(SELECT COLB FROM TBLB)

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

Example 3 

  SELECT * FROM TBLA WHERE COLA > ALL(SELECT COLB FROM TBLB)

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

Example 4 

  SELECT * FROM TBLA WHERE COLA > ALL(SELECT COLB FROM TBLB WHERE COLB<0)

Results in 1,2,3,4 and null. The fullselect returns no values. Thus, the result of the predicate is true for all rows in TBLA.

Example 5 

  SELECT * FROM TBLA WHERE COLA > ANY(SELECT COLB FROM TBLB WHERE COLB<0)

Results in Start of changean empty result tableEnd of change. The fullselect returns no values. Thus, the result of the predicate is false for all rows in TBLA.