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:

withtest_dataas(select'Chewbacca'wookie_name,86agefromdualunionallselectnull,nullfromdual)selecttd1.wookie_namewookie_name1,td2.wookie_namewookie_name2,casewhen(td1.wookie_nameisnotnullandtd2.wookie_nameisnotnullandtd1.wookie_name=td2.wookie_name)or(td1.wookie_nameisnullandtd2.wookie_nameisnull)then'equal'else'not equal'endnames_matchfromtest_datatd1crossjointest_datatd2

Pretty verbose and not exactly pretty. We can, however, remove theand 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:

withtest_dataas(select'Chewbacca'wookie_name,86agefromdualunionallselectnull,nullfromdual)selecttd1.wookie_namewookie_name1,td2.wookie_namewookie_name2,decode(td1.wookie_name,td2.wookie_name,'equal','not equal')names_matchfromtest_datatd1crossjointest_datatd2

And yes, it works for all data types:

withtest_dataas(select'Chewbacca'wookie_name,86agefromdualunionallselectnull,nullfromdual)selecttd1.ageage1,td2.ageage2,decode(td1.age,td2.age,'equal','not equal')age_matchfromtest_datatd1crossjointest_datatd2

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

withtest_dataas(select'Chewbacca'wookie_name,86agefromdualunionallselectnull,nullfromdual)selecttd1.wookie_namewookie_name1,td2.wookie_namewookie_name2fromtest_datatd1crossjointest_datatd2wheredecode(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 muchJacek Gebal for showing me this little trick)

The postDead easy NULL-aware comparison in Oracle with DECODE appeared first onDeveloper Sam.