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)-'
- Other comparison operators are also supported.1
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.
COLA |
---|
1 |
2 |
3 |
4 |
COLB | COLC |
---|---|
2 | 2 |
3 | – – |
COLB | COLC |
---|---|
2 | 2 |
COLA > ALL(SELECT COLB FROM TBLB
UNION
SELECT COLB FROM TBLC)
COLA > ANY(SELECT COLB FROM TBLB
UNION
SELECT COLB FROM TBLC)
COLA > ALL(SELECT COLC FROM TBLB
UNION
SELECT COLC FROM TBLC)
COLA > SOME(SELECT COLC FROM TBLB
UNION
SELECT COLC FROM TBLC)
COLA < ALL(SELECT COLB FROM TBLB WHERE COLB>3
UNION
SELECT COLB FROM TBLC WHERE COLB>3)
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.