Learn T-SQL With Erik: DISTINCT FROM?!
Video Summary
In this video, I dive into the exciting new `DISTINCT FROM` syntax introduced in SQL Server 2022, which simplifies how we handle nulls and equality checks in our queries. While it might not be as revolutionary as some of the other features that have been added over the years, it certainly offers a more elegant way to write certain types of queries. I walk through an example where `DISTINCT FROM` is used to replace clunky syntax for checking null values, demonstrating how it can improve readability and potentially performance by making us eligible for hash joins. Additionally, I explore its potential use in handling optional predicates, highlighting both the benefits and limitations of this feature.
Full Transcript
Erik Darling here, Darling Data. Woo! Look at those, look at those Darling Data colors, those beautiful teals. I wish that I could find adidas t-shirts with that same teal on the logo. Then it’d really, really be a company color man. Anyway, in this video, we’re going to talk about something, I guess, sort of new. I say sort of new. It came up with SQL Server 2022. And it is the, is it, well, the, the, the, distinct from syntax. So there is, is distinct from, and there is, is not distinct from. And we’re going to talk about that one here. Uh, even though, uh, you know, uh, SQL Server 2025 did not add anything nearly as cool as, is, is distinct from, even though distinct from is, is only kind of cool, uh, for, because like, when, I don’t know, we’ll, we’ll talk about in the video. Anyway, uh, all this course material is available at the presale price. And if you have a price of $250, it’ll go up to $500 at the end of the summer. So hop on that potato now before, before it, uh, gets, gets old and soggy. Cause you don’t, you don’t want to eat that. Anyway, uh, let’s talk about, uh, distinct from. So, uh, this, this query, if you watch a lot of my, if you watch my other videos, this query might look sort of familiar. I talked about, um, something like this in a video the other day, uh, where we had to write the query like this.
In order for it to be eligible for a hash join, right? So we write this query and we run this query. SQL Server will count, uh, will join the table, uh, post to the table comments on owner user ID being equal to user ID where, uh, owner user ID is null or they’re both null, right? Which is kind of nasty, clunky syntax. What distinct from allows you to do. And why, why it’s sort of cool is because you can just do this instead.
So you can say, select count from post join to comments on owner user ID is not distinct from user ID. And the distinct from stuff handles nulls without you having to write a whole bunch of extra or predicate nonsense. So that’s, that’s kind of nice.
And if we look at the estimated execution plan, we will see that we, we, we, we do, we are indeed eligible for a hash join. And if we run this, we will eventually get correct results back. And, and that, that is the same number that we got back last time.
So that’s, that’s, that’s neat and dandy there. Uh, prior to this, if you didn’t feel like writing all this wacky syntax for that, for, to check for this, you could write the query with a join on exists.
The number of people who I’ve met in my life who are aware that you can use an exists predicate in a join clause. It’s very small. The number of people who I know who are aware of the fact that you can write an exist clause with select a column with no from clause intersected to selecting another column with no from clause is even smaller.
In fact, the, the, the Venn diagram of people who know this, that this is a thing at all. And the people who need to buy, uh, my course content, uh, is, is actually a complete overlapping match, right? There, there are like you, you, you are in dire need of, of learning.
Uh, maybe not this specifically, but just T SQL in general. So if we, if we look at the query plan for this, we will see that we are still indeed eligible for yield hash join. This is just fine, right?
This is just an alternate way of expressing this. But this is just another one of those things where, uh, handling nulls gets very, very tricky in, in databases in general. SQL Server is not like a weird outlier in this regard.
Uh, if you look through anything about Postgres, Oracle, DB2, Snowflake, DuckDB, any of the new cool kid databases, what’s the other one? Red, Red Shaft or something. Uh, they all have the same weird null stuff going on that you have to be aware of.
So, but there are ways to write queries that are a little bit more, oh God, am I going to say it? They’re a little bit more elegant when it comes to handling nulls. So you could explicitly say join on this, or this is null and this is null.
Or you can say join on this is not distinct from this. Or you can say join on exists, blah, blah, blah, blah. Now, when I was initially talking about distinct from, I was, I was talking about how like, you know, like it’s only sort of cool, right?
It’s nice that it, you can do that with it. You can shorten up some queries. You can still get, you can still get good performance and all the other stuff.
But like one of, like one of the common things you’ll see when people start talking about, um, is distinct from, or is not distinct from, is the ability to sort of like handle optional predicates with it. So like, or optional parameters. So like, you know, like this, this right now, this is 22656.
And if we run this, we’re going to get this, this number back, right? And like, it’d be actually grab the execution plan for this. And if we look, this is, you know, we scan this table and we look at some stuff.
And like, you can, what you can do is you can say, well, null here, right? And you can say, oh, well, if this is null, then, you know, we’re going to do this. And, you know, you can put in is not distinct from, and you can do this, all right?
You can get some rows back. And what is neat is that when, with, you know, you can get a seek from some of these plans. And if we put 22656 back in here, then we’ll, you know, be eligible to, you know, get going and seek to some stuff.
But, like, the reason why I think this is only sort of cool is because, like, when you see something like this and you’re like, like, your mind immediately starts spinning and turning. And you’re like, oh, my God, I can go replace all these, like, optional parameter value searches that I have with, like, is distinct from or is not distinct from. And my life can get a lot easier.
The problem is that this only works like an equality predicate, right? And as soon as you get into adding, like, trying to, like, get, like, and or logic correct with this, you stop getting the results that you think you would get back. Like, stapling more stuff onto this gets real, real, real tricky to the point where I’m not even sure that, like, it means the same thing no matter how hard you try.
So, like, you know, like, think about all those optional parameter predicate queries you have where, you know, you might have some equality predicates in there, but then you’re also searching on, like, a date range or, like, a like or, you know, something like that where it’s not just a direct equality. This stuff just doesn’t handle those cases. So, it’s, if you truly just have a query that looks like this, and sometimes, you know, user ID will be null and someone wants to return, like, everything, and sometimes user ID will have a value and they just want to return results for that value.
Cool. I guess you can use it there instead of doing, like, you know, one of the billion other things that you can do that don’t require SQL Server 2022 to do it with. But, you know, I find the number of those cases to be very, very, very small. So, you know, when I said it’s just, it’s kind of cool, yeah, it is kind of cool.
It’s worth knowing about because it might save you some time and trouble for some other queries. But for this specific thing, it just doesn’t scratch the itch that I have. Anyway, that is just a little bit about Distinct From.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video where we will talk a little bit more about T-SQL. All right.
Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.