(1) >>-+-expression--+- = --+-------+-SOME-+--(--fullselect--)-------+->< | +- <> -+ +-ANY--+ | | +- < --+ '-ALL--' | | +- > --+ | | +- <= -+ | | '- >= -' | +-(--row-value-expression--)-- = --+-SOME-+--(--fullselect--)-+ | '-ANY--' | '-(--row-value-expression--)-- <> --ALL--(--fullselect--)-----'
A quantified predicate compares a value or values with a set of values.
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 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:
If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort 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 sort sequence.
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 subselect 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 subselect 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 subselect 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 subselect 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 the empty set. The subselect returns no values. Thus, the result of the predicate is false for all rows in TBLA.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.