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 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)
The post Dead easy NULL-aware comparison in Oracle with DECODE appeared first on Developer Sam.
Top comments (0)