7.19.7 Comparison predicate
Comparison predicates can be specified in search conditions. The following example illustrates a comparison predicate.
- Example:
-
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 products on or after September 6, 2011.
SELECT "USERID","PUR-CODE","PUR-DATE" FROM "SALESLIST" WHERE "PUR-DATE">=DATE'2011-09-06'Explanation
-
The underlined portion indicates the comparison predicate.
-
>= is called a comparison operator.
-
The terms on the left and right of a comparison operator are called comparison operands. In this example, the comparison operands are PUR-DATE (a column name) and DATE'2011-09-06' (a literal).
-
- Organization of this subsection
(1) Specification format
comparison-predicate ::= comparison-operand-1 comparison-operator comparison-operand-2
comparison-operand ::= value-expression
comparison-operator ::= {=|<>|!=|^=|<|<=|>|>=}
(2) Explanation of specification format
- comparison-operand-1, comparison-operand-2:
-
A comparison operand specifies a value such as a column name or literal. Comparison operands must be specified as value expressions. For details about value expressions, see 7.20 Value expression.
- comparison-operator:
-
The comparison operator is one of =, <>, !=, ^=, <, <=, >, or >=. The following table lists the meaning of each operator.
Table 7‒9: Meaning of comparison operators No.
Comparison operator
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
(3) Evaluation of the predicate
A comparison is TRUE if the comparison operands on the left and right of the comparison operator satisfy the comparison condition.
It is unknown if either of the comparison operands is the null value.
(4) Rules
-
The data types of the results of comparison-operand-1 and comparison-operand-2 must be data types that can be compared. For details about data that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.
-
When comparing numeric data, if the data being compared are of different data types, the comparison is performed using the data type with the wider range, as determined by the following hierarchy:
DOUBLE PRECISION > DECIMAL > INTEGER > SMALLINT
-
If the result of a comparison operand is the null value, the comparison result is unknown.
-
If you specify binary type data as the value expression of the comparison operands, you must specify =, <>, !=, or ^= as the comparison operator.
-
A comparison operand composed of only a dynamic parameter cannot appear on both sides of the comparison operator.
-
Example of a specification that is not permitted:?=?
-
Examples of a permissible specifications: C1=?, ?=10
-
-
For comparison-operand-2 you cannot specify the scalar function CONTAINS.
-
If you specify the scalar function CONTAINS for comparison-operand-1, specify > as the comparison operator. In this case, specify 0 for comparison-operand-2.
(5) Example
- Example
-
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.