Predicates and Search Condition › Expansion of Comparison-predicate

Expansion of Comparison-predicate

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:

( subquery )

Specifies a subquery that returns no more than one row and whose result table consists of a single column.

Note: 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.

Note: 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');