17-Feb-2016
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'
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'...
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.
Igor Kromin
Other posts you may like...