Basic predicate

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram                          (1)
>>-+-expression--+- = --+-------expression----------------------------+-><
   |             +- <> -+                                             |
   |             +- < --+                                             |
   |             +- > --+                                             |
   |             +- <= -+                                             |
   |             '- >= -'                                             |
   +-(--row-value-expression--)--+- = --+--(--row-value-expression--)-+
   |                             '- <> -'                             |
   +-(--fullselect--)--+- = --+--(--row-value-expression--)-----------+
   |                   '- <> -'                                       |
   '-(--row-value-expression--)--+- = --+--(--fullselect--)-----------'
                                 '- <> -'
 
Notes:
  1. Other comparison operators are also supported.

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

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:

The result of the predicate depends on the operator:

If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort 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 sort 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' )