15.00 - Nulls and CASE Expressions - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Nulls and CASE Expressions

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. Because of this, 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 as THEN clause conditions. The following example is valid.
  •      SELECT CASE
                 WHEN column_1 = 10
                 THEN ’NULL’
                END
         FROM table_1