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

Please leave your comments or feedback below!
comments powered by Disqus
Other posts you may like...
Hi! You can search my blog here ⤵
Or browse the recent top tags...

Recent Blog Posts

WebLogic Maven Plugin - How to fix the MojoExecutionException: The artifact location was not specified

jPhotoFrame version 0.4 released with a whole new layout engine

Upcycling a couple of old broken lamps to create something amazing

A custom exception mapper and writer for a RESTful JAX-RS Jersey service

How to fix Plex error - Sorry there was a problem playing this item

Jersey JAX-RS filters and interceptors execution order for a POST request

Fix your Mac - users not showing on the macOS login screen when FileVault is enabled

BMB-012 Nanoblock T-Rex Skeleton Model assembly

Writing a custom MessageBodyReader to process POST body data with Jersey

How to make Skype for Business responsive again on macOS

Recent Galleries

BMB-012 Nanoblock T-Rex Skeleton Model assembly

Tiny Arcade revision 6 kit assembly and decal application

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 1 - introduction and case disassembly

Building a custom Atari Lynx game box storage shelf unit in a day

Protecting old Atari Lynx game boxes with snug fit plastic sleeves

Monument Valley 2 is released and does not disappoint

Blogs and Friends

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity