SQL: WHEN CASE NULL fails.

Imagine you want to find all the null values in a column in a database table (SQL Server).

x
1
2
NULL
4
5

Here is the SQL that performs the task as required:

SELECT x,

CASE x

WHEN NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

The result he expected was:

x	result
1	no
2	no
NULL	yes
4	no
5	no

But that isn’t what he got. His result was like this:

x	result
1	no
2	no
NULL	no
4	no
5	no

Curiously, if you run this:

SELECT x,

CASE x

WHEN 1 THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

You do get this:

x	result
1	yes
2	no
NULL	no
4	no
5	no

 So, why didn’t the original work?

• Because NULL means unknown
• Because NULL does not equal NULL
• Because NULL is just weird, weird, weird

Anyway, here is the SQL that gives the expected answer: 

SELECT x,

CASE

WHEN x IS NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable