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:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
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:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN 1 THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
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:
[sourcecode language=”sql”]
SELECT x,
CASE
WHEN x IS NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

Leave a Reply

Your email address will not be published. Required fields are marked *