How You Can Use IS [NOT] DISTINCT FROM

One feature introduced with SQL Server 2022 that I’ve recently been playing around with is IS [NOT] DISTINCT FROM. This new feature can help when it comes to dealing with NULL value comparisons.

Comparing a NULL value with another value will return NULL. Think of it like multiplying anything by the number 0. It will always come back with 0. IS [NOT] DISTINCT FROM puts a spin on this by guaranteeing a true or false by assuming NULL is “something.”

Let’s look at an example of IS [NOT] DISTINCT FROM in action.

What Truly Counts

We’ll use a copy of the StackOverflow2013 database for this example. Let’s start with gathering the count of the Users table:

SELECT COUNT(*) FROM Users;
GO

We have 2465713 records in Users.

With that in mind, what if we want to find out how that total compares to Users with a Location of Ohio? We can pretty easily find Users with a Location of Ohio:

SELECT COUNT(*) FROM Users
WHERE Location = 'Ohio';
GO

We only have 648 Ohio records. But here is where things can potentially get tricky. If we make a small change to that query to find Users NOT in Ohio, we’ll get the following:

SELECT COUNT(*) FROM Users
WHERE Location != 'Ohio';
GO

588858 not Ohio combined with the 648 from Ohio falls well short of our 2465713 total. What’s going on? The difference is due to NULL values not being counted. Let’s use IS DISTINCT FROM and IS NOT DISTINCT FROM and see the differences.

IS [NOT] DISTINCT FROM Examples

First, if we look for Users where the Location is not distinct from Ohio, we’ll see the same 648 that we saw earlier:

SELECT COUNT(*) FROM Users
WHERE LOCATION IS NOT DISTINCT FROM 'Ohio';
GO

If we search for Users where the Location IS DISTINCT FROM Ohio, we’ll see a higher number than before:

SELECT COUNT(*) FROM Users
WHERE LOCATION IS DISTINCT FROM 'Ohio';
GO

It’s not necessarily right or wrong; it just depends on what you’re looking for.

Helpful?

SQL Server 2022’s IS [NOT] DISTINCT FROM feature offers some new flexibility with handling NULL values. Keep it in mind next time you’re dealing with NULL values in case it can help you out.

Thanks for reading!