CASE and Nulls - Analytics Database - Teradata Vantage

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, Vantage 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, Vantage 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.