# 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

## 5 thoughts on “Comparing NULLable Values”

1. I just wish they’d document (aka support) sys_op_map_nonnull :-(

2. tonyhasler says:

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.

• Thanks for your enlightening comment, Tony. Everybody read down to here!

3. Ananth says:

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