15.00 - CASE and Nulls - Teradata Database

CASE and Nulls

The ANSI SQL:2011 standard specifies that the CASE expression and its related expressions COALESCE and NULLIF must be capable of returning a null result.

Nulls and CASE Expressions

The rules for null usage in CASE, NULLIF, and COALESCE expressions are as follows.

If no ELSE clause is specified in a CASE expression and the evaluation falls through all the WHEN clauses, the result is null.

Nulls and expressions containing nulls are valid as value_expression_1 in a valued CASE expression.

The following examples are valid.

   SELECT CASE NULL

           WHEN 10 

           THEN 'TEN'

          END;

   SELECT CASE NULL + 1

           WHEN 10 

           THEN 'TEN'

          END;

Both of the preceding examples return NULL because no ELSE clause is specified, and the evaluation falls through the WHEN clause because NULL is not equal to any value or to NULL.

Comparing NULL to any value or to NULL is always FALSE. When testing for NULL, it is best to use a searched CASE expression using IS NULL or IS NOT NULL in the WHEN condition.

The following example is valid.

   SELECT CASE

           WHEN column_1 IS NULL

           THEN 'NULL'

          END

   FROM table_1;

Often, Teradata Database can detect when an expression that always evaluates to NULL is compared to some other expression or NULL, and gives an error that recommends using IS NULL or IS NOT NULL instead. Note that ANSI SQL does not consider this to be an error; however, Teradata Database reports an error since it is unlikely that comparing NULL in this manner is the intent of the user.

The following examples are not legal.

   SELECT CASE column_1

           WHEN NULL

           THEN 'NULL'

          END

   FROM table_1;

   SELECT CASE column_1

           WHEN NULL + 1

           THEN 'NULL'

          END

   FROM table_1;

   SELECT CASE

           WHEN column_1 = NULL

           THEN 'NULL'

          END

   FROM table_1;

   SELECT CASE

           WHEN column_1 = NULL + 1

           THEN 'NULL'

          END

   FROM table_1;

Nulls and expressions containing nulls are valid as THEN clause expressions.

The following example is valid.

   SELECT CASE

           WHEN column_1 = 10

           THEN NULL

          END

   FROM table_1

Note that, unlike the previous examples, the NULL in the THEN clause is an SQL keyword and not the value of a character literal.

CASE Shorthands

ANSI also defines two shorthand special cases of CASE specifically for handling nulls.

COALESCE expression (see “COALESCE Expression” on page 579)

NULLIF expression (see “NULLIF Expression” on page 581)