Quantified predicate

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


>>-+-expression--+-=------+--+-SOME-+--(fullselect1)---+-------><
   |             |    (1) |  +-ANY--+                  |   
   |             +-<>-----+  '-ALL--'                  |   
   |             +-<------+                            |   
   |             +->------+                            |   
   |             +-<=-----+                            |   
   |             '->=-----'                            |   
   +-row-value-expression-=-+-SOME-+-(fullselect2)-----+   
   |                        '-ANY--'                   |   
   |                           (1)                     |   
   '-row-value-expression---<>-------ALL-(fullselect2)-'

Notes:

Other comparison operators are also supported.¹

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

When the operator is ALL, 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 the operator is SOME or ANY, 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 fullselect2 must be the same as the number of value expressions specified by row-value-expression, and fullselect2 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 fullselect2. The value of the predicate depends on the operator that is specified:

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

Quantified predicates are equivalent to IN predicates. See Table 1 for some examples of equivalent quantified and IN predicates.

Examples: Use the following tables when referring to the following examples. In all examples, "row n of TBLA" refers to the row in TBLA for which COLA has the value n.

Table 1. TBLA
COLA
1
2
3
4

Table 2. TBLB
COLB	COLC
2	2
3	– –

Table 3. TBLC
COLB	COLC
2	2

Example 1: In the following predicate, the fullselect returns the values 2 and 3. The predicate is false for rows 1, 2, and 3 of TBLA, and is true for row 4.

  COLA > ALL(SELECT COLB FROM TBLB
         UNION
         SELECT COLB FROM TBLC)

Example 2: In the following predicate, the fullselect returns the values 2 and 3. The predicate is false for rows 1 and 2 of TBLA, and is true for rows 3 and 4.

  COLA > ANY(SELECT COLB FROM TBLB
         UNION
         SELECT COLB FROM TBLC)

Example 3: In the following predicate, the fullselect returns the values 2 and null. The predicate is false for rows 1 and 2 of TBLA, and is unknown for rows 3 and 4. The result is an empty table.

  COLA > ALL(SELECT COLC FROM TBLB
         UNION
         SELECT COLC FROM TBLC)

Example 4: In the following predicate, the fullselect returns the values 2 and null. The predicate is unknown for rows 1 and 2 of TBLA, and is true for rows 3 and 4.

  COLA > SOME(SELECT COLC FROM TBLB
         UNION
         SELECT COLC FROM TBLC)

Example 5: In the following predicate, the fullselect returns an empty result column. Hence, the predicate is true for all rows of TBLA.

  COLA < ALL(SELECT COLB FROM TBLB WHERE COLB>3
         UNION
         SELECT COLB FROM TBLC WHERE COLB>3)

Example 6: In the following predicate, the fullselect returns an empty result column. Hence, the predicate is false for all rows of TBLA.

  COLA < ANY(SELECT COLB FROM TBLB WHERE COLB>3
         UNION
         SELECT COLB FROM TBLC WHERE COLB>3)

If COLA were null in one or more rows of TBLA, the predicate would still be false for all rows of TBLA.