DB2 10.5 for Linux, UNIX, and Windows

Basic predicate

A basic predicate compares two values or compares a set of values with another set of values.


>>-+-expression--| comparison-operator |----expression-------------------+-><
   '-row-value-expression--| comparison-operator |--row-value-expression-'   

comparison-operator

|--+- = ------+-------------------------------------------------|
   |      (1) |   
   +- <> -----+   
   +- < ------+   
   +- > ------+   
   |      (1) |   
   +- <= -----+   
   |      (1) |   
   '- >= -----'

Notes:

The following forms of the comparison operators are also supported in basic and quantified predicates: ^=, ^<, ^>, !=, !<, and !>. In code pages 437, 819, and 850, the forms ¬=, ¬<, and ¬> are supported. All of these product-specific forms of the comparison operators are intended only to support existing SQL statements that use these operators, and are not recommended for use when writing new SQL statements.

The six comparison operators can effectively be expressed based on just two of the comparison operators. If the predicate operands are x and y, then the other four comparison operators can be expressed using the following alternative predicates.

Table 1. Predicates and alternative predicates
Predicate	Alternative predicate
x `<>` y	NOT (x `=` y)
x `>` y	y `<` x
x `<=` y	x `<` y OR x `=` y
x `>=` y	y `<` x OR x `=` y

When the predicate operands are specified as an expression, the data types of the expressions must be comparable. If the value of either operand is null, the result of the predicate is unknown. Otherwise the result is either true or false.

Table 2. Predicate evaluation with scalar operands
Predicate (with operand values x and y)	Boolean value	If and Only If...
x `=` y	is True	x is equal to y
x `<` y	is True	x is less than y
x `=` y	is False	x is not equal to y
x `<` y	is False	x `=` y is True or y `<` x is True

When the predicate operands are specified as a row-value-expression, they must have the same number of fields and the data types of the corresponding fields of the operands must be comparable. The result of the comparison is based on comparisons of the corresponding fields in the row-value-expression operands.

Table 3. Predicate evaluation with row operands
Predicate (with operand values Rx and Ry that have fields Rxi and Ryi where 0 < i < number of fields)	Boolean value	If and Only If...
Rx `=` Ry	is True	All pairs of corresponding value expressions are equal (Rxi `=` Ryi is True for all values of i).
Rx `<` Ry	is True	The first N pairs of corresponding value expressions are equal and the next pair has the left value expression less than the right value expression for some value of N (Rxi `=` Ryi is True for all values of i `<` n and Rxn `<` Ryn is True for some value of n).
Rx `=` Ry	is False	At least one pair of corresponding value expressions are not equal (NOT (Rxi `=` Ryi) is True for some value of i).
Rx `<` Ry	is False	All pairs of corresponding value expressions are equal (Rx `=` Ry is True) or the first N pairs of corresponding value expressions are equal and the next pair has the right value expression less than the left value expression for some value of N (Rxi `=` Ryi is True for all values of i < n and Ryn `<` Rxn is True for some value of n).
Rx `comparison operator` Ry	is Unknown	The comparison is neither True nor False.

Examples:

   EMPNO='528671'  
   SALARY < 20000 
   PRSTAFF <> :VAR1
   SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
  (YEARVAL, MONTHVAL) >= (2009, 10)