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 | (RowValueConstuctorList) | 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`	The syntax for expressions is defined under "Expression specification". 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. Scalar subqueries and their restrictions are defined under "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

Character strings are compared according to the ASCII collating sequence for ASCII data.
If there is a NULL value on either or both sides of a comparison predicate, the predicate evaluates to NULL and the row is not operated on.
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.

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.