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 |

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

Connor,

in 12c it seems to be documentated “a little bit”: http://orasql.org/2014/02/10/sys_op_map_nonnull-is-in-the-documentation-now/.

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!

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

DECODE is the way to go! This is one of a few differences between DECODE and CASE functions – how they compare NULLs:

SELECT DECODESELECT decode(a,b,1,0) decode_match, CASE a WHEN b THEN 1 ELSE 0 END case_match

FROM t(a,b,1,0) decode_match, CASE a WHEN b THEN 1 ELSE 0 END case_match

FROM t

my SQL needs to be corrected:

SELECT DECODE(a,b,1,0) decode_match, CASE a WHEN b THEN 1 ELSE 0 END case_match

FROM t