Row-Value-Expression in Db2
A row-value-expression allows you to
compare multiple columns (or values) at once as a tuple. Instead of writing
separate conditions for each column, you can group them together.
General Syntax
(COL1,
COL2, COL3) operator (Value1, Value2, Value3)
·
Both
sides must have the same number of values.
·
Data
types must be compatible.
·
Comparisons
are evaluated pair by pair.
·
If any
comparison involves NULL, the result may be Unknown.
Operators Explained with
Examples
1. Equality (=)
(COL1, COL2, COL3) = (100, 'X', 2000)
·
Row (100,
'X', 2000) → True
·
Row (100,
'Y', 2000) → False
· Row (100, NULL, 2000) → Unknown
2. Inequality (<>)
(COL1, COL2, COL3) <> (100, 'B', 500)
·
Row (100,
'B', 500) → False (all equal)
·
Row (100,
'C', 500) → True (COL2 <> 'B')
·
Row (101,
'B', 500) → True (COL1 <> 100)
3. Less Than (<)
(COL1, COL2, COL3) < (100, 'B', 500)
·
Row (100,
'A', 500) → True ('A' < 'B')
·
Row (99,
'Z', 999) → True (99 < 100)
·
Row (100,
'B', 400) → True (400 < 500)
4. Greater Than (>)
(COL1, COL2, COL3) > (100, 'B', 500)
·
Row (100,
'C', 500) → True ('C' > 'B')
·
Row (101,
'A', 300) → True (101 > 100)
·
Row (100,
'B', 600) → True (600 > 500)
5. Less Than or Equal (<=)
(COL1, COL2, COL3) <= (100, 'B', 500)
·
Row (100,
'B', 500) → True (exact match)
·
Row (100,
'A', 500) → True ('A' < 'B')
·
Row (99,
'Z', 999) → True (99 < 100)
6. Greater Than or Equal (>=)
(COL1, COL2, COL3) >= (100, 'B', 500)
·
Row (100,
'B', 500) → True (exact match)
·
Row (100,
'C', 500) → True ('C' > 'B')
·
Row (101,
'A', 300) → True (101 > 100)
The detailed IBM manual can be found at the
following link:
https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=predicates-basic-predicate#db2z_basicpredicate__fnocop
Quantified Predicates in Db2
A quantified predicate compares a
value (or a row of values) against a set of values returned by a
subquery (fullselect). It uses operators together with quantifiers like ALL, SOME,
or ANY.
General Syntax
expression operator {ALL | SOME | ANY}
(fullselect1)
(row-value-expression) operator {ALL | SOME |
ANY} (fullselect2)
·
expression
→ single value compared against a column of values.
·
row-value-expression
→ tuple of values compared against multiple columns.
·
fullselect
→ subquery returning one or more values.
Behavior of Quantifiers
1. ALL
- True if the relationship holds for every
value returned by the subquery.
- False if the relationship fails for at least
one value.
- Unknown if no comparison is false, but at least
one is unknown due to NULL.
- Special case:
If the subquery returns no rows, the result is True.
2. SOME / ANY
- True if the relationship holds for at
least one value returned by the subquery.
- False if the subquery is empty or the
relationship fails for all values.
- Unknown if no comparison is true, but at least
one is unknown due to NULL.
Examples
TBLA
TBLB
TBLC
Example 1: ALL
COLA > ALL (SELECT COLB FROM TBLB UNION
SELECT COLB FROM TBLC)
·
Subquery
returns {2, 3}.
·
Predicate
is False for rows 1, 2, 3.
·
True for row 4 (4 > 2 and 4 > 3).
Example 2: ANY
COLA > ANY (SELECT COLB FROM TBLB UNION
SELECT COLB FROM TBLC)
·
Subquery
returns {2, 3}.
·
Predicate
is False for rows 1, 2.
·
True for rows 3, 4 (3 > 2, 4 > 2 or 3).
Example 3: ALL with NULL
COLA > ALL (SELECT COLC FROM TBLB UNION
SELECT COLC FROM TBLC)
·
Subquery
returns {2, NULL}.
·
Predicate
is False for rows 1, 2.
·
Unknown for rows 3, 4 (because of NULL).
Example 4: SOME with NULL
COLA > SOME (SELECT COLC FROM TBLB UNION
SELECT COLC FROM TBLC)
·
Subquery
returns {2, NULL}.
·
Predicate
is Unknown for rows 1, 2.
·
True for rows 3, 4 (3 > 2, 4 > 2).
Example 5: Empty Result with ALL
COLA < ALL (SELECT COLB FROM TBLB WHERE
COLB > 3
UNION
SELECT COLB FROM TBLC WHERE COLB > 3)
·
Subquery
returns empty set.
·
Predicate
is True for all rows (special case).
Example 6: Empty Result with ANY
COLA < ANY (SELECT COLB FROM TBLB WHERE
COLB > 3
UNION
SELECT COLB FROM TBLC WHERE COLB > 3)
·
Subquery
returns empty set.
·
Predicate
is False for all rows.
The detailed IBM manual can be found at the
following link:
https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=predicates-quantified-predicate
