Nulls and CASE Expressions - Teradata Vantage

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.

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 another expression or NULL, and gives an error that recommends using IS NULL or IS NOT NULL instead. ANSI SQL does not consider this to be an error. However, Vantage reports an error because comparing NULL in this manner is unlikely 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;


The following example is valid.

   SELECT CASE
           WHEN column_1 = 10
           THEN NULL
          END
   FROM table_1


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