Dead easy NULL-aware comparison in Oracle with DECODE

One of the probably most unnerving things in databases is dealing with NULLs, especially when comparing data (e.g. looking for some data with the use of other data where you can’t be sure if either of them will be NULL).

Consider the following data:

If we would join that data with itself and compare the names, we would expect the following result:

This is achievable by the following SQL statement, using CASE…WHEN:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null                   , null   from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2,
  case when (
          td1.wookie_name is not null
      and td2.wookie_name is not null
      and td1.wookie_name = td2.wookie_name)
    or (
          td1.wookie_name is null
      and td2.wookie_name is null)
    then 'equal'
    else 'not equal'
  end names_match
from test_data td1
  cross join test_data td2

Pretty verbose and not exactly pretty. We can, however, remove the and td2.wookie_name is not null part, because if NAME1 is not NULL, a comparison with NULL will always fail.

But still, the comparison is very verbose and it’s a lot to type.

If we are on Oracle however, we can remove that whole CASE statement by one simple DECODE call:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null                   , null   from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2,
  decode(td1.wookie_name, td2.wookie_name, 'equal', 'not equal') names_match
from test_data td1
  cross join test_data td2

And yes, it works for all data types:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null                   , null   from dual
)
select
  td1.age age1,
  td2.age age2,
  decode(td1.age, td2.age, 'equal', 'not equal') age_match
from test_data td1
  cross join test_data td2

And yes, it can be easily used in the where clause:

with test_data as (
  select 'Chewbacca' wookie_name, 86 age from dual union all
  select null                   , null   from dual
)
select
  td1.wookie_name wookie_name1,
  td2.wookie_name wookie_name2
from test_data td1
  cross join test_data td2
where decode(td1.wookie_name, td2.wookie_name, 1, 0) = 1

So the next time you are comparing values that could be NULL and want to write a complicated CASE…WHEN statement – think about your friend DECODE and let it do its magic!

(Thank you very much Jacek Gebal for showing me this little trick)