Expansion of Comparison-predicate

The comparison-predicate tests whether a value is less than, equal to, or greater than another value.
idmscu19
The comparison-predicate tests whether a value is less than, equal to, or greater than another value.
Syntax
Expansion of comparison-predicate
►►─── 
value-expression
  
comparison-operator
 ─┬─ 
value-expression
 ─┬───────────►◄                                              └─ ( 
subquery
 ) ─────┘
Parameters
  • value-expression
    Specifies a value to be used in the comparison. For expanded
    value-expression
    syntax, see Expansion of Value-expression.
  • comparison-operator
    Specifies the comparison operator to be used in the test. Valid values for
    comparison-operator
    are the following:
    Comparison Operator
    Meaning
    =
    Equal to
    <>
    Not equal to
    <
    Less than
    <=
    Less than or equal to
    >
    Greater than
    >=
    Greater than or equal to
    Consideration
    The logical "not" operator can be specified using the code point represented by X'5F' that can vary with the code page being used. For example, the forms ¬=, ¬<, and ¬> are supported in code pages where the logical not sign is X'5F'. The forms ^=, ^<, and ^> are supported in code pages where the circumflex accent is represented by X’5F’. These forms of the operators are intended only to support existing SQL statements that use them and are not recommended for use when writing new SQL statements. The equivalent operator from the table above should be substituted for any operator which includes a not sign. For example, substitute '<>' for '¬=', '<=' for '¬>', and '>=' for '¬<'.
  • ( subquery )
    Specifies a subquery that returns no more than one row and whose result table consists of a single column. For more information about expanded
    subquery
    syntax, see Expansion of Subquery.
Usage
Comparable Data Types
The data types of the values being compared must be comparable. For information about comparing values of different data types, see Comparison, Assignment, Arithmetic, and Concatenation Operations.
Truth Value of a Comparison Predicate
The result of a comparison predicate is:
  • True: When the first value relates to the second value in the way specified by the comparison operator
  • False: When the first value does not relate to the second value in the way specified by the comparison operator
  • Unknown: When one or both of the values being compared are null or when the result of the subquery is an empty set
Example
As the Search Condition in a WHERE Parameter
The following DELETE statement deletes rows in the EXPERTISE table for employees who were terminated before January 1, 2006. The search condition in the WHERE parameter of the subquery consists of a single comparison predicate.
delete from expertise    where emp_id in       (select emp_id          from employee          where termination_date <'2006-01-01');