Comparison Operators
Purpose
Comparison operators, which are types of logical predicates (also called conditional expressions), test the truth of relations between expressions.
Comparison operators can appear in:
Syntax
where:
Syntax Element … |
Specifies … |
operator |
one of the comparison operators. |
expression_1 |
an SQL scalar expression, including derived period expressions. |
quantifier |
one of the following quantifier keywords: For information, see “ANY / ALL / SOME Quantifiers” on page 931. |
literal |
one or more literal values. A literal may be any of the following: The comparison operation may compare an expression against a list of explicit literals. The data types of expression and literal must be compatible. If the data types of the operands differ, Teradata Database performs an implicit conversion from one type to another in some cases. For details, see “Implicit Type Conversion of Comparison Operands” on page 500. |
subquery |
an SQL SELECT statement. Using a subquery in a condition is restricted in certain cases. |
scalar_expression |
an expression to be evaluated in comparison with a second scalar_expression, including derived periods. Comparison operators do not support BLOB or CLOB type expressions. You can explicitly cast BLOBs to BYTE or VARBYTE and cast CLOBs to CHARACTER or VARCHAR, and use the result with comparison operators. An expression that results in a UDT data type can only be compared with another expression that results in the same UDT data type. |
comparison_operator |
the type of comparison to be evaluated for truth. For a list of the supported comparison operators, see “Supported Comparison Operators” on page 495. |
Results
A logical expression that uses a comparison operator evaluates to TRUE, FALSE, or UNKNOWN.
ANSI Compliance
The following comparison operators are ANSI SQL:2011 compliant.
|
|
|
The following comparison operators are Teradata extensions to the ANSI SQL:2011 standard. Their use is deprecated.
|
|
|
Supported Comparison Operators
Teradata Database supports the following comparison operators.
ANSI Operator |
Teradata Extensions |
Function |
= |
EQ |
Tests for equality. |
<> |
^= |
Tests for inequality. |
< |
LT |
Tests for less than. |
<= |
LE |
Tests for less than or equal. |
> |
GT |
Tests for greater than. |
>= |
GE |
Tests for greater than or equal. |
Comparison Operators Using Subqueries
A subquery is a SELECT statement that returns values used to satisfy the comparison operation. The subquery must be enclosed in parentheses, and it does not end with a semicolon.
The subquery must refer to at least one table. A table that is in the WHERE clause, but that is not referred to in any other parts of the subquery, is not applicable.
A comparison operation may be used with a subquery whether or not a quantifier is used. If a quantifier is not used, however, then an error condition results if the subquery returns more than one value.
If a subquery returns no values, and if a quantifier is not used, then the result of the comparison is false. Therefore, if the following form is used, the subquery must return either no values (in which case the comparison evaluates to false), or it returns one value.
expression > (subquery)
With the following form, subquery must select the same number of expressions as are specified in the expression list.
The two expression lists are equal if each of the respective expressions are equal.
If the respective expressions are not equal, then the result of the comparison is determined by comparing the first pair of expressions (from the left) for which the comparison is not true.
A subquery in a comparison operation cannot specify a SELECT AND CONSUME statement.
Example
The following statement uses the ALL quantifier to compare two expressions with the values returned from a subquery to find the employee(s) with the most years of experience in the group of employees having the highest salary:
SELECT EmpNo, Name, DeptNo, JobTitle, Salary, YrsExp
FROM Employee
WHERE (Salary,YrsExp) >= ALL
(SELECT Salary,YrsExp FROM Employee) ;