Our Oracle database just keeps amazing me. Given the following testcase:
SELECT CASE WHEN NULL = '' THEN 1 ELSE 0 END as nullvsemptystring, CASE WHEN NULL = NULL THEN 1 ELSE 0 END as nullvsnull, CASE WHEN NULL = 'asdf' THEN 1 ELSE 0 END as nullvsstring FROM DUAL
nullvsemptystring - 0 nullvsnull - 1 nullvsstring - 0
But we got
nullvsnull - 0?
After a bit of digging on the internet i found this article by Robbert Vollman: Nulls in Oracle. Seems Oracle got me again.
To compare against NULL you need to use
IS NULL (explicitly saying it should be null)
SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END as nullvsnull FROM DUAL