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.

Read more about this topic:  Null (SQL), Comparisons With NULL and The Three-valued Logic (3VL), Effect of Null and Unknown in Other Constructs

Famous quotes containing the words case and/or expressions:

    My case is thrown exclusively upon the independent voters of this county, and if elected they will have conferred a favor upon me, for which I shall be unremitting in my labors to compensate.
    Abraham Lincoln (1809–1865)

    Many expressions in the New Testament come naturally to the lips of all Protestants, and it furnishes the most pregnant and practical texts. There is no harmless dreaming, no wise speculation in it, but everywhere a substratum of good sense. It never reflects, but it repents. There is no poetry in it, we may say, nothing regarded in the light of beauty merely, but moral truth is its object. All mortals are convicted by its conscience.
    Henry David Thoreau (1817–1862)