Comparisons That Produce TRUE Results
Conditions
The following table provides the conditions when comparisons produce TRUE results.
For simplicity, assume the syntax:
expression_1 — operator — expression_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 |
expression_1 > expression_2 is TRUE or |
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 |
||
|
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.”
|
|
|
|
|
|
|
predicate quantifiers: |
||