So it seems like the ISNULL and NULLIF functions are awesome for solving this kind of problem.

### The safe null-if-zero division problem

If we want to divide by*some-long-expression*and want to return NULL if it is zero, then we might find ourselves writing:

CASE WHENsome-long-expression= 0 THEN NULL ELSEnumerator/some-long-expressionEND

But we can avoid including some-long-expression twice by using:

numerator/ NULLIF(some-long-expression, 0)

### The inclusive/exclusive BETWEEN problem

The BETWEEN clause gives us*lower*<=

*test-value*<=

*upper,*including both lower and upper in the test range. But sometimes we might need lower only, or upper only, or both, to be exclusive tests.

This can lead to duplicating long expressions by having to write:

lower< (some-long-expression)AND (some-long-expression) < upper

But we can use NULLIF to similarly avoid repeating the long expression being tested:

NULLIF( NULLIF(some-long-expression, lower), upper) BETWEENlowerANDupper

*There's a few different combinations of scenarios we might need, as follows:*

Include Lower | Include Upper | Include Null | SQL |
---|---|---|---|

Yes | Yes | Yes | ISNULL(expr, lower) BETWEEN lower AND upper |

Yes | Yes | No | expr BETWEEN lower AND upper |

Yes | No | Yes | NULLIF(ISNULL(expr, lower), upper) BETWEEN lower AND upper |

Yes | No | No | NULLIF(expr, upper) BETWEEN lower AND upper |

No | Yes | Yes | NULLIF(ISNULL(expr, upper), lower) BETWEEN lower AND upper |

No | Yes | No | NULLIF(expr, lower) BETWEEN lower AND upper |

No | No | Yes | NULLIF(NULLIF(ISNULL(expr, some-midpoint) lower), upper) BETWEEN lower AND upper |

No | No | No | NULLIF(NULLIF(expr, lower), upper) BETWEEN lower AND upper |