Null (SQL) - Comparisons With NULL and The Three-valued Logic (3VL) - Effect of Null and Unknown in Other Constructs - CASE Expressions

CASE Expressions

SQL provides two flavors of conditional expressions. One is called "simple CASE" and operates like a switch statement. The other is called a "searched CASE" in the standard, and operates like a if...elseif.

The simple CASE expressions use implicit equality comparisons which operate under the same rules as the DML WHERE clause rules for Null. Thus, a simple CASE expression cannot check for the existence of Null directly. A check for Null in a simple CASE expression always results in Unknown, as in the following:

SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returned WHEN 0 THEN 'Is Zero' -- This will be returned when i = 0 WHEN 1 THEN 'Is One' -- This will be returned when i = 1 END FROM t;

Because the expression i = NULL evaluates to Unknown no matter what value column i contains (even if it contains Null), the string 'Is Null' will never be returned.

On the other hand, a "searched" CASE expression can use predicates like IS NULL and IS NOT NULL in its conditions. The following example shows how to use a searched CASE expression to properly check for Null:

SELECT CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULL WHEN i = 0 THEN 'Zero' -- This will be returned when i = 0 WHEN i = 1 THEN 'One' -- This will be returned when i = 1 END FROM t;

In the searched CASE expression, the string 'Null Result' is returned for all rows in which i is Null.

Oracle's dialect of SQL provides a built-in function DECODE which can be used instead of the simple CASE expressions and considers two nulls equal.

SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t;

Finally, all these constructs return a NULL if no match is found; they have a default ELSE NULL clause.