Manipulating Nulls | SQL Fundamentals | Teradata VantageCloud Lake - Manipulating Nulls - Teradata VantageCloud Lake

Nulls are not values and do not signify values. A null is a place holder indicating that no value is present. Therefore, you cannot solve for the value of a null. For example, the expression NULL = NULL is meaningless and therefore can never be true.

The following sections help you to understand how to use nulls in data manipulation statements and interpret the results of those statements.

Nulls and DateTime and Interval Data

A DateTime or Interval value is atomically null or 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, use a searched CASE expression with 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 can 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 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:

Trying to insert a row that matches any of these rows causes 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 a 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 typically 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 distinguish between values and aliases for nulls. BTEQ displays nulls as >?, which are not by themselves distinguishable from a CHAR or VARCHAR value of '?'.

Nulls and Aggregate Functions

With the exception of COUNT(*), aggregate functions ignore nulls in their arguments. This treatment of nulls is different from the way arithmetic operators and functions treat them.

This behavior can cause nontransitive anomalies. For example, if there are nulls in column A, column B, or both, the following expression may be true:

SUM(A) + (SUM B) <> SUM (A+B)

That is, 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 Information

• NULL Keyword as a Literal
• Rules on the result of expressions containing nulls, see CASE and Nulls.
• Rules for nulls in the following topics:
  • CASE Expressions
  • NULLIF Expression
  • COALESCE Expression
• COUNT(*) including nulls in its result, see COUNT.
• Nulls that have special considerations in the the following functions:
  • RANGE_N
  • CASE_N