Nulls are not valid as predicate conditions in SQL other than for the unique example of CASE expressions (see Nulls and CASE Expressions).
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 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, Excluding Nulls From Query Results and Searching for Nulls and Nonnulls In the Same Search Condition).
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.
Nulls and Arithmetic Operators and Functions
If an operand of any arithmetic operator or function is null, then the result of the operation or function is usually null. The following table provides some illustrations and some exceptions:
| WHEN the expression is … | THEN the result is … |
|---|---|
| 5 + NULL | null |
| LOG(NULL) | null |
| NULLIFZERO(NULL) | null |
| ZEROIFNULL(NULL) | 0 |
| COALESCE(NULL, 6) | 6 |
Nulls and Comparison Operators
If either operand of a comparison operator is null, then the result is unknown and an error is returned to the requestor. The following examples indicate this behavior.
| WHEN the expression is … | THEN the result is … | AND this error message returns to the requestor … |
|---|---|---|
| 5 = NULL | Unknown | 3731 |
| 5 <> NULL | ||
| NULL = NULL | ||
| NULL <> NULL | ||
| 5 = NULL + 5 |
Note that if the argument of the NOT operator is unknown, the result is also unknown. This evaluation translates to FALSE as a final boolean result.
Nulls and Aggregate Functions
With the important exception of COUNT(*), aggregate functions ignore nulls in their arguments. This treatment of nulls is very different from the way arithmetic operators and functions treat them, and is one of the major inconsistencies in the way SQL deals with nulls.
This behavior can result in apparent nontransitive anomalies. For example, if there are nulls in either column A or column B (or both), then the following expression is virtually always true.
SUM(A) + (SUM B) <> SUM (A+B)
In other words, for the case of SUM, the result is never a simple iterated addition if there are nulls in the data being summed.
The only exception to this is the case in which the values for columns A and B are both null in the same rows, because in those cases the entire row is not counted in the aggregation. This is a trivial case that does not violate the general rule.
The same is true, the necessary changes being made, for all the aggregate functions except COUNT(*), which does include nulls in its result.
- Define all NUMERIC columns as NOT NULL DEFAULT 0.
- Use the ZEROIFNULL function nested within the aggregate function to convert any nulls to zeros for the computation, for example
SUM(ZEROIFNULL(x) + ZEROIFNULL(y))
Nulls and DateTime and Interval Data
The general rule for managing nulls with DateTime and Interval data environments is that, for individual definitions, the rules are identical to those for handling numeric and character string values.
| WHEN any component of this type of expression is null … | THEN the result is … |
|---|---|
| Value | null. |
| Conditional | FALSE. |
| CASE | as it would be for any other CASE expression. |
DateTime or Interval values are defined to be either atomically null or atomically non-null. For example, you cannot have an interval YEAR TO MONTH value in which YEAR is null and MONTH is not.
Nulls and CASE Expressions
The ANSI/ISO SQL-2008 definitions for the CASE expression and its related expressions NULLIF and COALESCE specify that these expressions can return a null. Because of this, their behavior is an exception to the rules for all other predicates and expressions.
- If no ELSE clause is specified in a CASE expression and no WHEN clause evaluates to TRUE, then NULL is returned by default.
- Nulls and expressions containing nulls are valid as CASE conditions. The following examples are valid.
SELECT CASE NULL WHEN 10 THEN 'TEN' END; SELECT CASE NULL + 1 WHEN 10 THEN 'TEN' END;
- Nulls and expressions containing nulls are not valid as WHEN clause conditions. The following examples are not valid.
SELECT CASE column_1 WHEN NULL THEN 'NULL' END FROM table_1; SELECT CASE column_1 WHEN NULL + 1 THEN 'NULL' END FROM table_1; SELECT CASE WHEN column_1 = NULL THEN 'NULL' END FROM table_1; SELECT CASE WHEN column_1 = NULL + 1 THEN 'NULL' END FROM table_1;
The following example is valid.
SELECT CASE WHEN column_1 IS NULL THEN 'NULL' END FROM table_1;
- In contrast to the situation for WHEN clauses in a CASE expression, nulls and expressions containing nulls are valid in THEN and ELSE clauses. The following example is valid.
SELECT CASE WHEN column_3 = 'NULL' THEN NULL ELSE column_3 END FROM table_1;
NULLIF and COALESCE
The behavior of the CASE shorthand expressions NULLIF and COALESCE is the same as that for CASE with respect to nulls.
See NULLIF and COALESCE in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for further information.