DEV Community

Samuel Nitsche
Samuel Nitsche

Posted on • Originally published at developer-sam.de on

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:

NAME AGE
Chewbacca 86
NULL NULL

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

NAME1 NAME2 NAMES_MATCH
Chewbacca NULL not equal
Chewbacca Chewbacca equal
NULL NULL equal
NULL Chewbacca not equal

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

The post Dead easy NULL-aware comparison in Oracle with DECODE appeared first on Developer Sam.

Top comments (0)