Basic predicate

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

Read syntax diagramSkip visual syntax diagramexpressioncomparison-operatorexpressionrow-value-expressioncomparison-operatorrow-value-expressionboolean-expression
comparison-operator
Read syntax diagramSkip visual syntax diagram =  <> 1 <  >  <= 1 >= 1
Notes:

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.

Boolean values

You can use a basic predicate to compare a Boolean value with another Boolean value or with a value of a data type that can be cast to a Boolean value. A value of TRUE is greater than a value of FALSE. For example:
  • TRUE = 'on' is TRUE
  • DECFLOAT(4.3) = TRUE is TRUE
  • '0' <= FALSE is TRUE
  • 'yes' <= FALSE is FALSE

Alternative forms

For the comparison operators <>, <=, and >=, alternative forms are also supported. (The forms ¬=, ¬<, and ¬> are supported only in code pages 437, 819, and 850.) Support for these alternative forms is intended only to accommodate existing SQL statements; these forms are not recommended for new SQL statements.

Comparison Operator Alternative Forms
<> ^= != ¬=
<= ^> !> ¬>
>= ^< !< ¬<

Examples:

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