Null Follow-up: IS [NOT] DISTINCT FROM
Author: Craig Mullins | 3 min read | March 15, 2018
The IS [NOT] DISTINCT FROM is a SQL predicate operator introduced to db2 for z/OS in Version 8. But it has not received a lot of fanfare and many are unaware of its existence. It is quite convenient to use in situations where you are looking to compare to columns that could contain NULL.
Before diving into the operator, let’s first discuss the problem it helps to solve. Two columns are not equal if both are NULL, that is because NULL is unknown and a NULL never equals anything else, not even another NULL. But sometimes you might want to treat NULLs as equivalent. In order to do that, you would have to code something like this in your WHERE clause:
WHERE COL1 = COL2
OR (COL1 IS NULL AND COL2 IS NULL)
This coding would cause db2 to return all the rows where COL1 and COL2 are the same value, as well as all the rows where both COL1 and COL2 are NULL, effectively treating NULLs as equivalent. But this coding although relatively simply, can be unwieldy and perhaps, at least not at first blush, unintuitive.
Here comes the IS NOT DISTINCT FROM clause to the rescue. As of db2 V8, the following clause is logically equivalent to the one above, but perhaps simpler to code and understand:
WHERE COL1 IS NOT DISTINCT FROM COL2
The same goes for checking a column against a host variable. You might try to code a clause specifying WHERE COL = :HV :hvind (host variable and indicator variable). But such a search condition would never be true when the value in that host variable is null, even if the host variable contains a null indicator. This is because one null does not equal another null – ever. Instead we’d have to code additional predicates: one to handle the non-null values and two others to ensure both COL1 and the :HV are both null. With the introduction of the IS NOT DISTINCT FROM predicate, the search condition could be simplified to just:
WHERE COL1 IS NOT DISTINCT FROM :HV :hvind
This post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2010/10/null-follow-up-is-not-distinct-from.html
For additional resources please download white paper: “The Many Different Types of DBAs.”
