Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.
Notice: I'm taking a break from blogging to focus on Atari Gamer . com, check it out!

NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ for more details.
I've been writing some tricky SQL recently and came across an odd behaviour, at least I though it was odd at the time because I didn't read the documentation. I had two columns that represented a composite key, the first column was guaranteed to have a value and the second column could be null. Now I had to join this table to itself to get the results I needed and that required comparing this composite key. When I compared just the first column it worked fine, but as soon as I added the second column, the one that could have null values, nothing matched.

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'
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'...
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.



Skip down to comments...
A quick disclaimer...

Although I put in a great effort into researching all the topics I cover, mistakes can happen. If you spot something out of place, please do let me know.

All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle). Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...