Now with my experience in Java, if you compare two nulls you get a true result, but in Oracle it's quite different...
Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null.
Showing this in a simple SQL example...interestingly the selected value is '2'
SQL
select
case null
when null then '1'
else '2'
end null_test
from dual;
So what can you do? Luckily there is a handy function, NVL, that can convert a null value to a real value that can be compared.
Lets see it in action. I take my null value and convert it to a literal string 'null' and in this case the result is '1'...
SQL
select
case nvl(null, 'null')
when nvl(null, 'null') then '1'
else '2'
end null_test
from dual;
Of course this approach can be used in more complex queries and not just in a case statement. The application possibilities are quite vast.
Enjoy!
-i
