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..