When null == null isn't true and how to get around it

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;

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;

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