6 April 2018

Using NULLIF to avoiding duplicating long T-SQL sub expressions for safe division, exclusive between

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 numeratorsome-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
YesYesYes
ISNULL(expr, lower) BETWEEN lower AND upper
YesYesNo
expr BETWEEN lower AND upper
YesNoYes
NULLIF(ISNULL(expr, lower), upper) BETWEEN lower AND upper
YesNoNo
NULLIF(expr, upper) BETWEEN lower AND upper
NoYesYes
NULLIF(ISNULL(expr, upper), lower) BETWEEN lower AND upper
NoYesNo
NULLIF(expr, lower) BETWEEN lower AND upper
NoNoYes
NULLIF(NULLIF(ISNULL(expr, some-midpoint) lower), upper) BETWEEN lower AND upper
NoNoNo
NULLIF(NULLIF(expr, lower), upper) BETWEEN lower AND upper