NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-53
Comparison Predicate
Comparison Predicate
The comparison predicate compares the values of two expressions, the values of two
sets of expressions, or the value of an expression and a single value that is the result of a
subquery.
Considerations—Comparison Predicate
•
General considerations for comparisons
The two row-value-specs must contain the same number of expressions.
Two row-value-specs are equal if all values at the same ordinal position are
equal. Their relation is determined by comparing the values in the first ordinal
position. If these values are equal, the values in the next ordinal position are
compared, and so forth, until the exact relation is determined.
The data type of the first expression must be compatible with the data type of the
second expression. The data type of an expression in the first row-value-spec
must be compatible with the data type of the corresponding expression in the second
row-value-spec.
The subquery result must be a single value. If the subquery evaluates to more than
one row, the comparison results in an error. If no rows satisfy the search condition of
the subquery, the predicate evaluates to null.
You cannot use a comparison predicate in a WHERE, ON, or HAVING clause to
retrieve all rows where expression is null; use the IS NULL predicate instead.
•
Comparing character data
You can compare two character strings only if both strings are associated with the
same character set.
{ expression comparison-op { expression } }
{ { subquery } }
{ }
{ row-value-spec comparison-op row-value-spec }
comparison-op is:
= Equal
<> Not equal
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
row-value-spec is:
{ expression [ , expression ] ... }
{ ( expression [ , expression ] ... ) }