7.20.7 Comparison predicate

(1) Specification format

comparison-predicate::= comparison-operand-1 comparison-operator comparison-operand-2
 
  comparison-operand::= {value-expression|row-value-constructor}
  comparison-operator::= {=|<>|!=|^=|<|<=|>|>=}

To Page Top

(2) Explanation of specification format

comparison-operand-1, comparison-operand-2:

The comparison operand is specified in the form of a value expression or a row value constructor. For details about value expressions, see 7.21 Value expression. For details about row value constructors, see 7.18 Row value constructors.

The specification rules for specifying row value constructors for comparison operand are as follows:

• When specifying a row value constructor for a comparison operand, two or more row value constructor elements must be specified. If only one row value constructor element is specified, the row value constructor is treated as a value expression enclosed in parentheses.

  Example of being treated as a row value constructor

  ("C1","C2") = (1,2)

  Example of being treated as a value expression

  ("C1") = (1)

  The above is the same as "C1" = 1.

• When specifying a row value constructor for a comparison operand, specify row value constructors for both the left and right comparison operands of the comparison operator.

comparison-operator:

The comparison operator is one of =, <>, !=, ^=, <, <=, >, or >=. The following table lists the meaning of each operator.

Table 7‒13: Meaning of comparison operators

No.	Comparison operator specification	Meaning
1	X = Y	X and Y are equal
2	X <> Y X != Y X ^= Y	X and Y are not equal
3	X < Y	X is less than Y
4	X <= Y	X is less than or equal to Y
5	X > Y	X is greater than Y
6	X >= Y	X is greater than or equal to Y

Legend:

X and Y: Comparison operands

Important

When specifying a row value constructor for a comparison operand, you can specify the comparison operators =, <>, !=, and ^= of No. 1 to 2 in the table above. The comparison operators No. 3 to 6 cannot be specified.

To Page Top

(3) Evaluation of predicates

■Specifying a value expression to a comparison operand

It is True if the comparison operands on either side of the comparison operator satisfy the comparison conditions.

It is undefined if either of the comparison operands are a null value.

■Specifying a row value constructor to a comparison operand

The value of each row value constructor element on both sides of the operands of the comparison operator is compared to determine the result.

(Example)

For comparison operator "="

True if all the values of the row value constructor elements being compared are equal.

False if one or more of the values of the row value constructor elements being compared are not equal.

Undefined if the row value constructor elements has one or more null values and all the values of the row value constructor elements being compared (except the values of the row value constructor element to which the null value and null value are being compared) are equal.

(Example)

Specification example of comparison predicate	Comparison predicate result
(1,2,3) = (1,2,3)	True
(1,2,3) = (1,2,4)	False
(1,2,3) = (1,null value,4)	False
(1,2,3) = (1,2,null value)	Undefined
(1,2,null value) = (1,2,null value)	Undefined

For comparison operators "<>", "!=", "^="

True if one or more of the values of the row value constructor elements being compared are not equal.

False if all the values of the row value constructor elements being compared are equal.

Undefined if the row value constructor elements has one or more null values and all the values of the row value constructor elements being compared (except the values of the row value constructor element to which the null value and null value are being compared) are equal.

(Example)

Specification example of comparison predicate	Comparison predicate result
(1,2,3) <> (1,2,4)	True
(1,2,3) <> (1,null value,4)	True
(1,2,3) <> (1,2,3)	False
(1,2,3) <> (1,2,null value)	Undefined
(1,2,null value) <> (1,2,null value)	Undefined

To Page Top

(4) Rules

To Page Top

(5) Examples

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased product code P001 or P003 on or after September 4, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-04'
        AND ("PUR-CODE"='P001' OR "PUR-CODE"='P003')

The underlined portions show the comparison predicates.

The comparison operand specifies a value expression.

Example 2

Retrieve the customer ID (USERID) U001 and product code (PUR-CODE) P001 from the sales history table (SALESLIST).

SELECT "USERID","PUR-CODE","PUR-NUM"
  FROM "SALESLIST"
  WHERE ("USERID","PUR-CODE") = ('U001','P001')

The underlined portions show the comparison predicates.

The comparison operand specifies a row value constructor.

To Page Top