Manipulating Nulls | SQL Fundamentals | Teradata Vantage - Manipulating Nulls - Advanced SQL Engine - Teradata Database

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.

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 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, 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 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:

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 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(*).

COUNT(*) includes nulls in its result.

RANGE_N and CASE_N Functions

Nulls have special considerations in the RANGE_N and CASE_N functions.

Related Topics