SQL: IS DISTINCT FROM

Daily Knowledge Drop

SQL Server 2022 introduced a new predicate IS [NOT] DISTINCT FROM, which allows for two expressions to be compared, but takes NULL values into account.

Data setup

Consider a Blog table, with a nullable DateCreated column - this column could have a valid DateTime value, but could also contain NULL values.

Pre SQL 2022

Prior to SQL 2022, when comparing two values, NULL values were, by default, not taken into account.

Assume the Blog table DOES contain rows which have NULL DateCreated values:

DECLARE @createdDate datetime

SELECT @createdDate = NULL

SELECT *
FROM Blog
WHERE DateCreated = @createdDate

0 rows returned

Even though @createdDate is NULL, and there are rows in the table with NULL, no rows are returned - NULL values are not taken into account.

To successfully return rows which do contain NULL values, both expressions need to be converted to the same default value in case of a NULL value:

DECLARE @createdDate datetime

SELECT @createdDate = NULL

SELECT *
FROM Blog
WHERE ISNULL(DateCreated, '1900-01-01') = 
	ISNULL(@createdDate, '1900-01-01')

10 rows returned

With this technique, NULL values on either expression is converted to '1900-01-01', which is then successfully compared.

With SQL Server 2022, performing this comparison becomes much easier.

SQL 2022

SQL Server 2022 introduces the new IS [NOT] DISTINCT FROM predicate - this allows the comparison of values while taking NULL values into account.

DECLARE @createdDate datetime

SELECT @createdDate = NULL

SELECT * 
FROM Blog
WHERE DateCreated IS NOT DISTINCT FROM @createdDate

10 rows returned

The IS [NOT] DISTINCT FROM predicate compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL

Notes

A relatively minor update, but for a developer which writes a good amount SQL this small update definitely does make things simpler and easier.

References

Cool Stuff in SQL Server 2022 – IS DISTINCT FROM

Daily Drop 232: 11-01-2023

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.