Comparison Predicate

A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression relates to the second expression as specified by the comparison operator.

SQL syntax

RowValueConstructor CompOp RowValueConstructor2

The syntax for RowValueConstructor: 

RowValueConstructorElement | (RowValueConstructorList) | ScalarSubquery

The syntax for RowValueConstructorList: 

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructor2 (one expression) 

Expression

The syntax for RowValueConstructor2 (list of expressions) 

((Expression[,...]))

The syntax for CompOp: 

{= | <> | > | >= | < | <= }

Parameters

Component Description

Expression

See "Expression Specification" for information on syntax. Both numeric and non-numeric expressions are allowed in comparison predicates, but both expressions must be compatible with each other.

ScalarSubquery

A subquery that returns a single value. See "Subqueries" for information on scalar subqueries.

=

Is equal to.

<>

Is not equal to.

>

Is greater than.

>=

Is greater than or equal to.

<

Is less than.

<=

Is less than or equal to.

Description

  • All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

  • If RowValueConstructorList is specified only the operators = and <> are allowed.

  • See "Numeric Data Types" for information about how TimesTen compares values of different but compatible types.

  • If either side of a comparison predicate evaluates to UNKNOWN or NULL, this implies that neither the predicate nor the negation of the predicate is TRUE.

  • The NULL value itself can be used directly as an operand of an operator or predicate. For example, the (1 = NULL) comparison is supported. This is the same as if you cast NULL to the appropriate data type, as follows: (1 = CAST(NULL AS INT)). Both methods are supported and return the same results.

Examples

Retrieve part numbers of parts requiring fewer than 20 delivery days:

SELECT PartNumber FROM Purchasing.SupplyPrice 
WHERE DeliveryDays < 20;

The query returns the last_name of employees where salary=9500 and commission_pct=.25.

Note:

The expression on the right side of the equal sign must be enclosed in double parentheses (( )). 

Command> SELECT last_name FROM employees 
         WHERE(salary,commission_pct) = ((9500,.25));
< Bernstein >
1 row found.

The query returns the last_name of the employee whose manager_id = 205. The employee's department_id and manager_id is stored in both the employees and departments tables. A subquery is used to extract the information from the departments table. 

Command> SELECT last_name FROM employees 
         WHERE (department_id, manager_id) =
           (SELECT department_id, manager_id FROM departments 
            WHERE manager_id = 205);
< Gietz >
1 row found.