Dead easy NULL-aware comparison in Oracle with DECODE
Tags: 100codeexamples, sql, oracle
Published: 2023-02-24 14:53:57

---
title: Dead easy NULL-aware comparison in Oracle with DECODE
published: true
date: 2023-01-16 21:56:41 UTC
tags: 100CodeExamples,SQL,Oracle
canonical_url: https://developer-sam.de/2023/01/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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.linkedin.com/in/jacek-gebal-6678301) for showing me this little trick)

The post [Dead easy NULL-aware comparison in Oracle with DECODE](https://developer-sam.de/2023/01/dead-easy-null-aware-comparison-in-oracle-with-decode/) appeared first on [Developer Sam](https://developer-sam.de).