Comparing NULLable Values

If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here’s why – and how.

Test Data

drop table t purge;
create table t(rn, a, b) as
select 1, 1, 1 from dual union all
select 2, 1, null from dual union all
select 3, null, 1 from dual union all
select 4, null, null from dual;

select * from t;
RN A B
1 1 1
2 1
3 1
4

 

Trying to compare

Usually, we would expect A and B to be equal when both are 1, or when both are NULL. Oracle, following the SQL standard, disagrees:

select * from t
where a = b;
RN A B
1 1 1

 

What about testing for inequality? We would expect lines 2 and 3 to be returned, but no:

select * from t
where a != b;
RN A B

 

To sum up, no comparison involving a NULL will ever be TRUE.

Testing explicitly for NULL

The standard way of testing for NULL involves the “IS NULL” expression. Here’s how to test for equality.

select * from t
where a = b
or (a is null and b is null);
RN A B
1 1 1
4

 

Testing for inequality requires three comparisons.

select * from t
where a != b
or (a is null and b is not null)
or (a is not null and b is null);
RN A B
2 1
3 1

 

Using NVL

A common way of simplifying the comparison is to convert NULL to some value that will (hopefully) never be in the real data.

select * from t
where nvl(a,-9999) = nvl(b,-9999);
RN A B
1 1 1
4

 

I have never liked this method, because someday, somehow that “impossible” value will find its way into the data. Also, there has to be a special “impossible” value for each datatype.

Using DECODE: my favorite

DECODE actually does what I usually want: it considers two NULL values to be equivalent, and a NULL and non-NULL to be different. No need for an “impossible” value, and it works the same for all datatypes.

select * from t
where decode(a,b,0,1) = 0;
RN A B
1 1 1
4
select * from t
where decode(a,b,0,1) = 1;
RN A B
2 1  
3   1
Advertisements

5 thoughts on “Comparing NULLable Values

  1. One thing to bear in mind when deciding on how to formulate a predicate is the impact on the CBO’s cardinality estimate. In fact, the DECODE construct is transformed into a SYS_OP_MAP_NONNULL predicate for you. Even NVL is recognised by the CBO and column statistics are used in cardinality calculations. But I have just started working with an application where COALESCE is in vogue. The CBO doesn’t treat COALESCE(a,-1) = COALESCE(b,-1) in the special way it treats NVL(a,-1) = NVL(b,-1)…it just applies the standard 1% selectivity.

  2. The purpose of lnnvl function can be useful if there are any NULLs when performing comparisions..

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s