Comparisons involving NULL-able values and columns are an early learning experience. I recently stumbled upon a relatively new way to handle these which I thought would be interesting T-SQL to share.
Let’s start with an example, retrieving paid invoices for a particular date:
SELECT InvoiceId, CustomerId, InvoiceValue
FROM dbo.Invoices
WHERE PaidDate = @SearchDate;
If we pass NULL in to retrieve unpaid invoices we’d change the query something like this:
WHERE PaidDate = @SearchDate
OR (PaidDate IS NULL
AND @SearchDate IS NULL)

…or…
WHERE ISNULL(PaidDate, '1900-01-01')
= ISNULL(@SearchDate, '1900-01-01')
/* Assuming 1900-01-01 isn't a valid date! */

It’s the curse of NULLs, the price we pay. As you can see from the execution plans, different ways to solve this have performance implications.
SQL Server 2022 arrived with a new weapon in the syntax wars – in the form of IS [NOT] DISTINCT FROM.
This is a predicate which performs equality comparisons, but crucially handles NULL values. IS DISTINCT FROM identifies records which don’t match the comparison value, whereas IS NOT DISTINCT FROM finds records which do match, like = but NULL-aware.
That’s it, simple. The same query can now be written as:
SELECT InvoiceId, CustomerId, InvoiceValue
FROM dbo.Invoices
WHERE PaidDate IS NOT DISTINCT FROM @SearchDate;
Performance wise this predicate allows index seeks so the plan matches the OR example above. It’s no more performant, but it’s shorter.
This new predicate solves one of those long standing paper-cuts, but I’ve never seen it in production or even in LLM-generated outputs. How come?
- Reuse of the common
DISTINCTkeyword in SQL Server is confusing, albeit the syntax aligns to an ANSI standard supported by other platforms like PostgreSQL - Equality requires a double-negative (
IS NOT [DIFFERENT]) which makes mental parsing overly complex - It’s not much more concise than what it’s replacing. I’d have gone for
==and=/=, but hey.
So we have yet another tool in the toolbox. It’s slightly shorter than its predecessors, and avoids the scan, but there’s too much mental overhead for me when skimming a script with it in.
I like new tools, but this one’s left-handed. Although I’m left-handed too, I work better with right-handed tools – the ones I’ve practised with and everyone knows. So for now, this one isn’t quite for me.