I seem to find myself generating a lot of SQL from other higher-level query structures. And it's bugged me for a while that if I have some long SQL expression, and then need to perform certain operations such as division, or exclusive BETWEEN, then it gets repeated.

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 WHEN *some-long-expression *= 0 THEN NULL ELSE *numerator* / *some-long-expression *END

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*) BETWEEN *lower *AND *upper*

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 |