When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do so. In this video I’ll walk through some of the example and you’ll see it is pretty easy to use.
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
Hey, this is Kris from Essential SQL, and if you find yourself working with nulls and want to replace these missing values within your queries, then watch this video.
All right, let’s get right into it.
What I’m going to do today is show you how to use the ISNULL function to take a null and replace it with another value that you can show in your results of your query.
So let’s start out with a sample query that I’ve put together. Let me get this, paste it in. And you’re going to see that this is just a list of products from the Venture Works database. You’ll see that for some of the products, the color is null.
What we’d like to do is change that to the color to unknown. So, how can we do that? So we want to change the value. I’m going to change it from a NULL to some other value.
Well, I can use the ISNULL function, and I can come in here and it’s really pretty simple. I could just say ISNULL. I’m going to do lower case to be consistent.
Aliasing Columns Using ISNULL
Now, you may wonder why I have color here and color at the end. That’s just an alias on the column, right? If I run it without the alias, see it says no column name. It doesn’t know what to call the result of this expression.
So I just tell it, “You know what? Just call it the same thing as color was,” and then everybody’s cool. If people needed to use this elsewhere, if it was in a view or something else, they’ll know it’s pretty obvious that it’s the color.
Okay, so that’s how easy it is to use ISNULL, so let me talk about one issue that will trip you up. It’s a data typing issue, and it’s kind of interesting.
ISNULL and Data Conversions
What it is, is that when the ISNULL is doing its conversion it will implicitly convert based on the data type of color. So in our case here, I think color was wide enough.
Let’s say it’s like a VARCHAR(20), so it can hold let’s say 20 characters. So ISNULL will be able to be implicitly converted into color, because unknown is less than 20 characters and it won’t get chopped off. But if color was smaller, like three or four characters, then our ISNULL function would come back differently.
So let me show you that here, and what we’ll do is bring in a tsql script. So we’re going to just comment this part out. We don’t need that anymore, and then I have this data type issues.
I’m going to set up color as a four-character variable, and then if I say select at color … Whoops, I’ve got to type right. This is just pretty straightforward. It might look weird, but all I’m doing is saying take a variable. Let’s declare it. Let’s set it to red, and they just tell me what’s inside the variable and you can see it comes back as red. All right, so now let’s set color.
Let’s do this. Let’s add the ISNULL function here, right? And then I’ll say unknown, and then I think we’ll just call it quits at that. When I run this, you’ll see it comes back as red because there’s no issue with it being null. Now, let’s change color to null. This is where you’re going to see the issue.
So now what’s going to happen is, is you’re going to see this get chopped off. And why is that? Because it’s taking the value of four. So if you know that you’re always going to have this issue, like sometimes you can go and look at the tables and you can see what the value is, you could cast this.
So I could say, CAST color as VARCHAR(10), and then when I run it, it’ll come back with unknown because now it’s implicitly made this. This is VARCHAR(10), so when it goes to do the implicit conversion, unknown’s going to fit inside. Okay, that’s one way to deal with this.
Using COALESCE instead of ISNULL
Another is to use COALESCE. So if I did a … Which is a sister function to ISNULL.
You can use either to replace missing values (NULL).
If I said select COALESCE at color and then unknown, what you’ll see is that this is going to pick the first non-null that it can find, and then output it.
To make sure that we don’t muddy the waters, let’s just get rid of the original ISNULL, try a COALESCE on this, and you’ll see it comes back with unknown. And so here it’s not implicitly converting the data type.
It was four characters, but it will return a type that also is able to accommodate our value of unknown. So that’s also like a slight introduction to COALESCE, but I really wanted to talk about ISNULL.
Conclusion
ISNULL is compact and very easy to use, and it’s a super-great way to use it in your results to and other expressions, to replace missing values such as NULL.
So I can, just to give you a quick side-by-side, we’ll just do this. And then when I run it, you’ll see here’s the color null. I changed them to the ISNULL value, and it’s a super-great way to be able to eliminate those nulls when you’re working with either text or numbers.
All right, have a great day. 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