15.00 - Logical and Arithmetic Operations on Nulls - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Logical and Arithmetic Operations on Nulls

Nulls are not valid as predicate conditions in SQL other than for the unique example of CASE expressions (see “Nulls and CASE Expressions” on page 685).

You cannot solve for the value of a null because, by definition, it has no value. For example, the expression value = NULL has no meaning and therefore can never be true (however, see “Null Sorts as the Lowest Value in a Collation” on page 687 for a counterexample). A query that specifies the predicate WHERE value = NULL is not valid because it can never be true or false. The meaning of the comparison it specifies is not only unknown, but unknowable.

If you want to search for fields that do or do not contain nulls, you must use the operators IS NULL or IS NOT NULL (see “Searching for Nulls Using a SELECT Request” on page 687, “Excluding Nulls From Query Results” on page 688 and “Searching for Nulls and Nonnulls In the Same Search Condition” on page 688).

The difference is that when you use a mathematical operator like =, you specify a comparison between values or value expressions, whereas when you use the IS NULL or IS NOT NULL operators, you specify an existence condition. Note that even though IS NULL and IS NOT NULL return truth values, their operands are not truth values; therefore they are not logical operators and do not count against the number of possible logical operators for a trivalent logic calculated in “Number of Logical Operators Supported for Bivalent and Trivalent Logics” on page 677.