Nulls and CASE Expressions - Teradata Vantage

The ANSI/ISO SQL-2008 definitions for the CASE expression and its related expressions NULLIF and COALESCE specify that these expressions can return a null. Therefore, their behavior is an exception to the rules for all other predicates and 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 no WHEN clause evaluates to TRUE, then NULL is returned by default.

Nulls and expressions containing nulls are valid as CASE conditions. The following examples are valid.

     SELECT CASE NULL
             WHEN 10
             THEN 'TEN'
            END;
     SELECT CASE NULL + 1
             WHEN 10
             THEN 'TEN'
            END;

Nulls and expressions containing nulls are not valid as WHEN clause conditions. The following examples are not valid.

     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;

The following example is valid.

     SELECT CASE
             WHEN column_1 IS NULL
             THEN 'NULL'
            END
     FROM table_1;

In contrast to the situation for WHEN clauses in a CASE expression, nulls and expressions containing nulls are valid in THEN and ELSE clauses. The following example is valid.
```
     SELECT CASE
             WHEN column_3 = 'NULL'
             THEN NULL
             ELSE column_3
            END
     FROM table_1;
```