Manipulating Nulls
Nulls are neither values nor do they signify values. They represent the absence of value. A null is a place holder indicating that no value is present.
You cannot solve for the value of a null because, by definition, it has no value.
For example, the expression NULL = NULL has no meaning and therefore can never be true. A query that specifies the predicate
WHERE NULL = NULL is not valid because it can never be true. The meaning of the comparison it specifies
is not only unknown, but unknowable.
These properties make the use and interpretation of nulls in SQL problematic. The following sections outline the behavior of nulls for various SQL operations to help you to understand how to use them in data manipulation statements and to interpret the results those statements affect.
NULL Literals
See “NULL Keyword as a Literal” on page 107 for information on how to use the NULL keyword as a literal.
Nulls and DateTime and Interval Data
A DateTime or Interval value is either atomically null or it is not null. For example, you cannot have an interval of YEAR TO MONTH in which YEAR is null and MONTH is not.
Rules for the Result of Expressions That Contain Nulls
For more rules on the result of expressions containing nulls, see the sections that follow and SQL Functions, Operators, Expressions, and Predicates.
Nulls and Comparison Operators
If either operand of a comparison operator is null, then the result is unknown. If either operand is the keyword NULL, an error is returned that recommends using IS NULL or IS NOT NULL instead. The following examples indicate this behavior.
5 = NULL
5 <> NULL
NULL = NULL
NULL <> NULL
5 = NULL + 5
If the argument of the NOT operator is unknown, the result is also unknown. This translates to FALSE as a final boolean result.
Instead of using comparison operators, use the IS NULL operator to search for fields that contain nulls and the IS NOT NULL operator to search for fields that do not contain nulls. For details, see “Searching for Nulls” on page 142 and “Excluding Nulls” on page 142.
Using IS NULL is different from using the comparison operator =. When you use an operator like =, you specify a comparison between values or value expressions, whereas when you use the IS NULL operator, you specify an existence condition.
Rules for Nulls and CASE Expressions
For details on the rules for nulls in CASE, NULLIF, and COALESCE expressions, see SQL Functions, Operators, Expressions, and Predicates.
Excluding Nulls
To exclude nulls from the results of a query, use the operator IS NOT NULL.
For example, to search for the names of all employees with a value other than null in the jobtitle column, enter the statement.
SELECT name
FROM employee
WHERE jobtitle IS NOT NULL ;
Searching for Nulls
To search for columns that contain nulls, use the operator IS NULL.
The IS NULL operator tests row data for the presence of nulls.
For example, to search for the names of all employees who have a null in the deptno column, you could enter the statement:
SELECT name
FROM employee
WHERE deptno IS NULL ;
This query produces the names of all employees with a null in the deptno field.
Searching for Nulls and Non-Nulls Together
To search for nulls and non-nulls in the same statement, the search condition for nulls must be separate from any other search conditions.
For example, to select the names of all employees with the job title of Vice Pres, Manager, or null, enter the following SELECT statement.
SELECT name, jobtitle
FROM employee
WHERE jobtitle IN ('Manager', 'Vice Pres') OR jobtitle IS NULL ;
Including NULL in the IN list has no effect because NULL never equals NULL or any value.
Null Sorts as the Lowest or Highest Value in a Collation
When you use an ORDER BY clause to sort records, Teradata Database sorts null as the lowest or highest value.
If any row has a null in the column being grouped, then all rows having a null are placed into one group.
NULL and Unique Indexes
For unique indexes, Teradata Database treats nulls as if they are equal rather than unknown (and therefore false).
For single-column unique indexes, only one row may have null for the index value; otherwise a uniqueness violation error occurs.
For multicolumn unique indexes, no two rows can have nulls in the same columns of the index and also have non-null values that are equal in the other columns of the index.
For example, consider a two-column index. Rows can occur with the following index values:
|
Value of First Column in Index |
Value of Second Column in Index |
|
1 |
null |
|
null |
1 |
|
null |
null |
An attempt to insert a row that matches any of these rows will result in a uniqueness violation.
Replacing Nulls With Values on Return to Client in Record Mode
When Teradata Database returns information to a client system in record mode, nulls must be replaced with some value for the underlying column because client system languages do not recognize nulls.
The following table shows the values returned for various column data types.
|
Data Type |
Substitute Value Returned for Null |
|
CHARACTER(n) DATE TIME TIMESTAMP INTERVAL |
Pad character (or n pad characters for CHARACTER(n), where n > 1) |
|
PERIOD(DATE) |
8 binary zero bytes |
|
PERIOD(TIME [(n)]) |
12 binary zero bytes |
|
PERIOD(TIME [(n)] WITH TIME ZONE) |
16 binary zero bytes |
|
PERIOD(TIMESTAMP [(n)] [WITH TIME ZONE]) |
0-length byte string |
|
BYTE[(n)] |
Binary zero byte if n omitted else n binary zero bytes |
|
VARBYTE(n) |
0‑length byte string |
|
VARCHARACTER(n) |
0‑length character string |
|
BIGINT INTEGER SMALLINT BYTEINT FLOAT DECIMAL REAL DOUBLE PRECISION NUMERIC |
0 |
The substitute values returned for nulls are not, by themselves, distinguishable from valid non-null values. Data from CLI is normally accessed in IndicData mode, in which additional identifying information that flags nulls is returned to the client.
BTEQ uses the identifying information, for example, to determine whether the values it receives are values or just aliases for nulls so it can properly report the results. BTEQ displays nulls as >?, which are not by themselves distinguishable from a CHAR or VARCHAR value of '?'.
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.
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 disregarded 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(*).
If this property of nulls presents a problem, you can always do either of the following
workarounds, each of which produces the desired result of the aggregate computation
SUM(A) + SUM(B) = SUM(A+B).
NOT NULL DEFAULT 0.
SUM(ZEROIFNULL(x) + ZEROIFNULL(y))
which produces the same result as this:
SUM(ZEROIFNULL(x) + ZEROIFNULL(y)).
COUNT(*) includes nulls in its result. For details, see SQL Functions, Operators, Expressions, and Predicates.
RANGE_N and CASE_N Functions
Nulls have special considerations in the RANGE_N and CASE_N functions. For details, see SQL Functions, Operators, Expressions, and Predicates.