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.
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
Other posts you may like...