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]