Oracle Null Safe Comparison (Spoiler alert: SYS_OP_MAP_NONNULL)

Even most experienced programmers are caught null comparison trap time to time. Let’s say we have two different customer tables and our business need is to find the customers that has the same first name, middle name and last name in both table. And let’s say we can join the tables on their respective id columns.

customer_data_1
customer_data_2

When we run the below query:

select t1.id, t1.first_name, t1.middle_name, t1.last_name
from customer_data_1 t1
join customer_data_2 t2 on t1.id = t2.id
where t1.first_name = t2.first_name
and t1.middle_name = t2.middle_name
and t1.last_name = t2.last_name;

Result going to be like this:

result_null

As you can see, we’ve missed the customers 4 and 6 even though their first name’s and last name’s are equal. To solve this issue, one way is to use nvl function and use a value that can not be exist in both table:

select t1.id, t1.first_name, t1.middle_name, t1.last_name
from customer_data_1 t1
join customer_data_2 t2 on t1.id = t2.id
where t1.first_name = t2.first_name
and nvl(t1.middle_name, 'XXXXX') = nvl(t2.middle_name, 'XXXXX')
and t1.last_name = t2.last_name;

With the help of nvl function we are able to retrieve those missing 2 columns:

result_nonavoided

However, sometimes we can not be sure that this value going to be exist or not. Another method to solve this problem is to use undocumented function SYS_OP_MAP_NONNULL. This function converts values to byte code and for null it generates FFFF. Let’s run the same query using this function:

select t1.id, t1.first_name, t1.middle_name, t1.last_name
from customer_data_1 t1
join customer_data_2 t2 on t1.id = t2.id
where t1.first_name = t2.first_name
and SYS_OP_MAP_NONNULL(t1.middle_name) = SYS_OP_MAP_NONNULL(t2.middle_name)
and t1.last_name = t2.last_name;

And the result is null safe comparison:

result_nonavoided

As you can see, we can avoid null trap using undocumented SYS_OP_MAP_NONNULL function.

Developers Rock!!!

Advertisements
This entry was posted in Oracle and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s