Basic predicate

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


>>-+-expression--+- = --+--expression---------------------+----><
   |             +- <> -+                                 |   
   |             +- < --+                                 |   
   |             +- > --+                                 |   
   |             +- <= -+                                 |   
   |             '- >= -'                                 |   
   +-row-value-expression--+- = --+--row-value-expression-+   
   |                       '- <> -'                       |   
   +-(--fullselect--)--+- = --+--row-value-expression-----+   
   |                   '- <> -'                           |   
   '-row-value-expression--+- = --+--(--fullselect--)-----'   
                           '- <> -'

Notes:

¹ Other comparison operators are also supported.¹

When a single expression is specified on the left side of the operator, another expression must be specified on the right side. The data types of the corresponding expressions must be compatible. The value of the expression on the left side is compared with the value of the expression on the right side. If the value of either operand is null, 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 (= or <>) and another row-value-expression is specified on the right side of the operator, both row-value-expressions must have the same number of value expressions. The data types of the corresponding expressions 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.

When a row-value-expression is specified and a fullselect is also specified:

SELECT * is not allowed in the outermost select lists of the fullselect.
The result table of the fullselect must have the same number of columns as the row-value-expression. The data types of the corresponding expressions of the row-value-expression and the fullselect 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:

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.
- Otherwise, unknown (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 is:
- True if any one pair of corresponding value expressions evaluates to false.
- False if all pairs of corresponding value expressions evaluate to true.
- Otherwise, unknown (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 true).

If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating 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 collating sequence.

For values x and y:

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 greater than or equal to y
x<= y: x is less than or equal to y

Examples

Example 1

  EMPNO = '528671'

  PRTSTAFF <> :VAR1

  SALARY + BONUS + COMM < 20000

  SALARY > (SELECT AVG(SALARY) 
            FROM EMPLOYEE)

Example 2: List the name, first name, and salary of the employee who is responsible for the 'OP1000' project.

  SELECT  LASTNAME, FIRSTNME, SALARY
    FROM  EMPLOYEE X
    WHERE  EMPNO = ( SELECT  RESPEMP
                       FROM PROJA1 Y
                       WHERE MAJPROJ = 'OP1000' )

¹ The following forms of the comparison operators are also supported in basic and quantified predicates: !=, !<, !>, ¬=,¬<, and¬> are supported. All 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.

Some keyboards must use the hex values for the not (¬) symbol. The hex value varies and is dependent on the keyboard that is used. A not sign (¬) or the character that must be used in its place in certain countries or regions, can cause parsing errors in statements passed from one database server 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 '¬<'.