Basic predicate
A basic predicate compares two values or compares a set of values with another set of values.
>>-+-expression-+-=------+-expression----------------------+--->< | | (1) | | | +-<>-----+ | | +-<------+ | | +->------+ | | +-<=-----+ | | '->=-----' | | (1) | '-row-value-expression--+-=-----+--row-value-expression-' '-<>----'
- Other comparison operators are also supported.1
When expression is a fullselect, the fullselect must return a single result column with a single value, whether null or not null. If the value of either operand is null or the result of the fullselect is empty, the result of the predicate is unknown. Otherwise, the result is either true or false.
When a row-value-expression is specified on the left side of the operator, another row-value-expression, with an identical number of value expressions, must be specified on the right side. The data types of the corresponding expressions or columns of the row-value-expressions must be compatible.
The value of each expression on the left side is compared with the value of its corresponding expression on the right side. The result of the predicate depends on the operator, as in the following:
- If the operator is =, the result of the predicate is:
- True - if all pairs of corresponding value expressions evaluate to true.
- False - if any one pair of corresponding value expressions evaluates to false.
- Unknown - if the comparisons is neither true nor false. That is, if at least one comparison of corresponding value expressions is unknown because of a null value and no pair of corresponding value expressions evaluates to false.
- If the operator is <>, the result of the predicate (Rx1,Rx2,...,Rxn)
<> (Ry1,Ry2,...,Ryn) is:
- True - if, and only if, Rxi=Ryi evaluates to false for some value of i. That is, there is at least one pair of non-null values, Rxi and Ryi, that are not equal to each other.
- False - if, and only if, Rxi=Ryi evaluates to true for every value of i. That is, Rx1,Rx2,...,Rxn)=(Ry1,Ry2,...,Ryn) is true.
- Unknown - if the comparison is neither true nor false. That is, Rxi or Ryi is a null value for some value of i, and there is no value of j such that Rxj=Ryj evaluates to false.
Predicate | Is true if and only if ... |
---|---|
x = y | x is equal to y |
x <> y | x is not equal to y |
x < y | x is less than y |
x > y | x is greater than y |
x <= y | x is less than or equal to y |
x >= y | x is greater than or equal to y |
EMPNO = '528671'
SALARY < 20000
PRSTAFF <> :VAR1
SALARY >= (SELECT AVG(SALARY) FROM DSN8A10.EMP)
SELECT LASTNAME, FIRSTNAME, SALARY
FROM DSN8A10.EMP X
WHERE EMPNO = (
SELECT RESPEMP
FROM PROJA1 Y
WHERE MAJPROJ = 'SECRET'
UNION
SELECT RESPEMP
FROM PROJA2 Z
WHERE MAJPROJ = 'SECRET');
A logical not sign (¬) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target CCSIDs. To avoid this problem, substitute an equivalent operator for any operator that includes a not sign. For example, substitute '<>' for '¬=', '<=' for '¬>', and '>=' for '¬<'.