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.