15.00 - Comparison Operators - Teradata Database

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:

IF, WHILE, REPEAT, and CASE expressions in stored procedures

WHEN clauses in searched CASE expressions

WHERE, ON, and HAVING clauses to qualify or disqualify rows in a SELECT statement

CASE_N functions

Syntax

where:

Syntax Element …	Specifies …
operator	one of the comparison operators.
expression_1 expression_2	an SQL scalar expression, including derived period expressions.
quantifier	one of the following quantifier keywords: ANY SOME ALL For information, see “ANY / ALL / SOME Quantifiers” on page 931.
literal	one or more literal values. A literal may be any of the following: Defined value Macro parameter Built-in value such as TIME, DATE, or USER 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.

EQ ^= NE

NOT= LT LE

GT GE

Supported Comparison Operators

Teradata Database supports the following comparison operators.

ANSI Operator	Teradata Extensions	Function
=	EQ	Tests for equality.
<>	^= NE NOT=	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) ;