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

Did you like this post or found it useful? Considering supporting this Blog to keep its web servers running, any amount helps! Thanks!
Have comments or feedback on what I wrote? Please share them below!
comments powered by Disqus
Other posts you may like...