CASE and Nulls
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.
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, 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;
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.
CASE Shorthands
ANSI also defines two shorthand special cases of CASE specifically for handling nulls.