Using NULLIF to compare missing values to NULL is handy. It become especially useful when you find yourself working with a column contains both empty and NULL text. NULLIF provide handy and compact way to compare both types of data to NULL itself.
If you like what you are seeing, then why not get some super cool training? Signup for our next free FearlessSQL course training here!
Table of contents
Now just sit back and enjoy our ISNULL video!
Transcript from Video
If you have to deal with data that’s somewhat inconsistent, like you have blanks and NULL, and you’re trying to figure out how to filter that information and missing values, then you’re going to want to watch this video.
So hey, this is Kris from Essential SQL, and today I want to introduce to you a function that’s kind of esoteric, it’s different, it’s weird. You don’t use it all the time. But when you do need to use it, it’s very handy and it makes your SQL very compact, and the function is called NULLIF.
So let’s set up the scenario and then I’m going to walk you through one case where I find using NULLIF very useful.
So let’s assume that I have some data here where I have a MiddleName and I have a MiddleName where I have blanks, like here with Sam and Hazem, and then I also have maybe missing values where it’s NULL.
What I want to be able to do is filter out those folks that have either a NULL or a blank.
I want to come up with a handy, compact way of doing that, because as you can imagine, as your queries get really large, being able to have things succinct and compact makes it easier to read your queries.
Using Database Transactions
Before we go on, just notice that what I’ve done here is I’ve kind of wrapped everything in a transaction because the database, the Adventure Works database doesn’t really give you a good way to show this example so I kind of manufactured some data.
I just basically changed some of the names to blank, and I wrapped it in a transaction because that way, when I’m done with the example, it goes back to its original state.
For instance, if I just run this select right here, you can see that has Hazem and Sam now have values on it. So that’s the idea. So when I run it again and you’ll see, they have blanks.
We’re going to run everything inside here where the good stuff happens, and that’s where it’ll be like our little test lab.
Using NULLIF with Missing Values
So let’s introduce NULLIF, and then I’m going to show you some other ways that you could do this using SQL, because there’s always more than one way to, kind of, in a sense, per se, get through these examples.
I’m going to copy in same statement. But now what we’ve done is we’ve added a WHERE clause and we got this funky thing called NULLIF here. And it’s like… I don’t know when I first looked at this I’m like,
“What? You got NULLIF, and I got NULL.” It’s like, “What are we trying to do here?”
So let me explain what’s going on. I think you and I together will be a lot more clear on this.
What’s happening here is that we’re going to look at the MiddleName column. And if the MiddleName is blank, like Hazem and Sam are, then we’re going to set these column values to NULL.
Basically it’s doing exactly what the function says, make it NULL if this condition holds. And the condition’s inequality.
So make it NULL if MiddleName equals blank. That’s what that’s saying right here. Make it NULL if MiddleName equals blank.
Maybe it’s a good way to remember that.
NULLIF rock at Comparing Missing Values
Now that I have that as NULL for my blanks, the cool thing is I have all my missing values on an even playing ground and to pull them out of the database, all I have to do is compare them to NULL. So the records that are for Kim and then the ones that are blank for Hazem and Sam will all surface because, through the magic of the NULLIF function and just inherently having the… It’s not a value. Having NULL, they will show.
Let’s try this. So here we have our results and you may be going, “Well wait a minute, I don’t see where Hazem and Sam are null.”
Well, that’s true because the NULLIF is only operating on the column as it applies to the WHERE condition, not in the slack.
So we’re not displaying a modified results as a result of the function; only using it when we do the compare.
Using CASE in place of NULLIF
Let me show you an equivalent way of doing this using a CASE statement. So I’m going to put these two things side by side for a second, and… Oops, I forgot my SELECT statement.
Let’s just bring this in too… It’s important. And maybe this’ll be… This is kind of strung out. So let’s do… This will make this a little better to read, because it’s kind of buried. So this CASE statement here, it’s like this. Okay, now I can maybe explain this to you and it’ll read it a little better.
So I think we now understand how NULLF works. I’m going to change MiddleName to NULL if it’s blank.
In this case here, what we’re saying is if the MiddleName is blank, then make it NULL; otherwise, just leave it alone.
And then this case expression, a value that gets returned, will be tested against NULL. And that is what we’ll then use to do our filtering.
Using IIF instead of NULLIF to Compare Missing Values
Let me comment this one out right here, and let’s run it now on the case statement.
And you can see it comes back with the same result. Because we can do one last type, let me show you how this can be done with an inline IF (IIF), which will work in SQL server, but not necessarily in the data warehouse.
I think inline IFs aren’t available there yet, so probably 90% of you folks could use this. But really what’s happening here is this is saying if MiddleName equals blank, then make it a NULL, otherwise just set the value to the MiddleName and then test against NULL. When I run that I get the same result.
NULLIF is Compact
So let’s compare a couple of these and talk about, real quickly, why NULLIF is cool to use with missing values.
One reason that I like this is that when you’re working with just testing values and you get comfortable with it, this becomes a very compact form.
If you know you’re always dealing with blanks and NULL, then this becomes a compact task.
This is much more compact than having to write this out; I have just one statement. There’s less errors to make. I’m only specifying my column once as opposed to twice. Less characters to type in and just less chances to goof it up. And likewise with the inline IF, it’s the same thing where it’s just less chance to make a booboo along the way, and to be honest, again, the code’s a little more robust.
This is a little more compact than the case statement, but in my opinion, it might be a little more harder to read. I don’t know, I find this sometimes harder to read than even a CASE statement.
I’m just so used to reading case statements. I work with a data warehouse, and that’s all we use there. I can read these much easier than inline IF, even though I know this is test then else.
For some reason this is just a little tougher, especially if they get nested.
Conclusion
Anyways, point is that I think when you’re just trying to do a simple comparison where you’re looking at missing values, such as null values and blanks, that this is a super pattern to use to do that comparison.
So hopefully you found this to be a really good tip. And subscribe to my channel because I have other good tips and I will be showing you more good tips in the future, and I’d appreciate that.
Do not forget to go to essentialsql.com. Check out the site. Go to the tutorials. Sign up for Fearless SQL. There’s a wait list, and I’ll talk to you later. Bye.
Leave a Reply