15.00 - Manipulating Nulls - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

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

  • 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 non-null argument.
  • 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

  • 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.
  • 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).

  • 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. 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.