Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

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'
 SQL
select
case null
when null then '1'
else '2'
end null_test
from dual;

sqlnull1.png




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;

sqlnull2.png


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

Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...