15.00 - Comparisons That Produce TRUE Results - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Comparisons That Produce TRUE Results

Conditions

The following table provides the conditions when comparisons produce TRUE results.

For simplicity, assume the syntax:

expression_1 operatorexpression_2

expression_1 and expression_2 must contain the same number of scalar values and range from 1 through n rows, represented by r, so that the rth components of expression_1 and
expression_2 are expression_1r and expression_2r.

The δth item in the range is notated as row δ such that the δth component of expression_1 is notated as expression_1δ and the δth component of expression_2 is notated as expression_2δ.

The data types of expression_1 and expression_2 must be compatible. If the data types of the expressions 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.

For an explanation of the symbols used in this table, see “Predicate Calculus Notation Used In This Book” on page 1372.

 

This comparison …

Is TRUE iff …

expression_1 = expression_2

r, expression_1r = expression_2r is TRUE.

expression_1 <> expression_2

δ such that expression_1δ <> expression_2δ is TRUE.

expression_1 < expression_2

δ such that expression_1δ < expression_2δ is TRUE and for all r < δ, expression_1r = expression_2r is TRUE.

expression_1 > expression_2

δ such that expression_1δ >expression_2δ is TRUE and for all r > δ, expression_1r = expression_2r is TRUE.

expression_1 <= expression_2

expression_1 < expression_2 is TRUE or
expression_1 = expression_2 is TRUE.

expression_1 => expression_2

expression_1 > expression_2 is TRUE or
expression_1 = expression_2 is TRUE.

Null Expressions

If any expression in a comparison is null, the result of the comparison is unknown.

For a comparison to provide a TRUE result when comparing fields that might result in nulls, the statement must include the IS [NOT] NULL operator.

Floating Point Expressions

Calculations involving floating point values often produce results that are not what you expect. If you perform a floating point calculation and then compare the results against some expected value, it is unlikely that you get the intended result.

Instead of comparing the results of a floating point calculation, make sure that the result is greater or less than what is needed, with a given error. Here is an example:

   SELECT i, SUM(a) as sum_a, SUM(b) as sum_b
   FROM t1
   GROUP BY i
   HAVING ABS(sum_a - sum_b) > 1E-10;

For more information on potential problems associated with floating point values in comparison operations, see SQL Data Types and Literals.

More Information on Comparison Operators

 

FOR more information on …

SEE …

using comparison operators in conditional expressions in searched CASE expressions

Chapter 12: “CASE Expressions.”

using comparison operators in conditional expressions in WHERE, ON, or HAVING clauses in SELECT statements

“The SELECT Statement” in SQL Data Manipulation Language.

using comparison operators in conditional expressions in IF, WHILE, or REPEAT statements in stored procedures

SQL Stored Procedures and Embedded SQL.

other comparison operator, including:

Chapter 20: “Logical Predicates.”

 

  • [NOT] EXISTS
  • [NOT] IN
  • LIKE
  • IS [NOT] NULL
  • [NOT] BETWEEN … AND …
  • predicate quantifiers:

  • ALL
  • ANY
  • SOME