16.20 - CASE and Nulls - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.