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.
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
- When any component of a value expression is null, then the result is null.
- The result of a conditional expression that has a null component is unknown.
- If an operand of any arithmetic operator (such as + or -) or function (such as ABS or SQRT) is null, then the result of the operation or function is null with the exception of ZEROIFNULL. If the argument to ZEROIFNULL is NULL, then the result is 0.
- COALESCE, a special shorthand variant of the CASE expression, returns NULL if all its arguments evaluate to null. Otherwise, COALESCE returns the value of the first argument that has a value that is not null.
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.
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
- CASE and its related expressions COALESCE and NULLIF can return a null.
- NULL and null expressions are valid as the CASE test expression in a valued CASE expression.
- When testing for NULL, it is best to use a searched CASE expression using the IS NULL or IS NOT NULL operators in the WHEN clause.
- NULL and null expressions are valid as THEN clause conditions.
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 Values that Are Not Null Together
To search for nulls and values that are not null 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, Vantage 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, Vantage 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 values that are not null and 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|
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 Vantage 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.
|Data Type||Substitute Value Returned for Null|
|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|
The substitute values returned for nulls are not, by themselves, distinguishable from valid values that are not null. 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(*).
- Always define NUMERIC columns as NOT NULL DEFAULT 0.
- Use the ZEROIFNULL function within the aggregate function to convert any nulls to zeros for the computation, for example
SUM(ZEROIFNULL(x) + ZEROIFNULL(y))
which produces the same result as this:
SUM(ZEROIFNULL(x) + ZEROIFNULL(y)).
COUNT(*) includes nulls in its result.
RANGE_N and CASE_N Functions
Nulls have special considerations in the RANGE_N and CASE_N functions.
- How to use the NULL keyword as a literal, see NULL Keyword as a Literal.
- Rules on the result of expressions containing nulls, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- Rules for nulls in CASE, NULLIF, and COALESCE expressions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- COUNT(*) including nulls in its result, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- Nulls that have special considerations in the RANGE_N and CASE_N functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.