Functions for NULL values

In this video segment, we’re going to discuss how to use functions to work with null values, specifically the ISNULL and the COALESCE function. So let’s get straight into the demonstration.

As you can see, I’ve already written a SELECT statement to select the FirstName, LastName, and MiddleName from the Person.Person table. You will also notice that some of our people do not have a middle initial.

Now a quick review. To display only the people who do not have a middle initial, I’ll use a WHERE statement, WHERE, middle, name IS NULL, and this will display approximately 8,500 people who do not have a middle initial.

Now if I wanted to see the people who did have a middle initial, I would use the WHERE IS NOT NULL, WHERE IS NOT NULL. Execute that code, and you can see there are about 11,473 people who do have a middle initial.

Now what I want to do is I want to display all my people, and if they have a middle initial, display that middle initial. But if they don’t, I do not want to see the null values. I want to display an empty string.

I’m going to start off first, we no longer need this WHERE statement, so I want to highlight that and delete. But for the MiddleName field, I want to type in ISNULL, because I’m using the ISNULL function. So if the MiddleName has a value, it is going to display that value. If not, I want it to display an empty string, or more specifically, single quote, single quote, and close parentheses. And I will alias this as MiddleName. I’ll execute my code, and you could see the people who did have a middle initial, it’s still displaying their middle initial, but the people who did not have a middle name is now showing an empty string.

I could have put in a different value if I wanted to, such as if I want to put in NA for not available. That could have been used as well. If I execute, you can see now that I have people whose middle name is now NA. So we’ll go ahead and leave that as an empty string. So I’ll execute that code again, and we can see it actually puts that as an empty string.

Now the ISNULL function is really good if I’m just working with a single field, if I just want to see if a field or a column has a null value or not. Now another function we have is the COALESCE function. The COALESCE function allows me to work with more than one field or to combine fields into a single column based off whether those buyers are null or not.

So the first thing I’m going to do to demonstrate the COALESCE function is to create a new table named Person.PhoneContact that will have four fields– ContactID, HomePhone, WorkPhone, and CellPhone. So if they have a home phone number, that’s the number I want to use. If they do not, then want to use their work phone number, and then finally, if they have neither, a home phone number or a work phone number, I want to have their cell phone number as my point of contact. So I want to create that table and insert four records. So I’ll go ahead and execute this code. And now I have four rows in that table.

Now to see the COALESCE function, I’m going to open up a new tab, and you can see I’ve already written a SELECT statement to select all the records from that Person.PhoneContact table that we just created. And you see, contact ID number 1 has a home phone, a work phone, but does not have a cell phone. Contact ID number 2 does not have a home phone number, but does have a work phone and a cell phone.

Contact ID number 3 does not have a home phone or work phone, so I would have to contact them on their cell phone. And finally, contact ID number 4 does not have a home phone but does have a work phone. So I want to coalesce or combine these into a single column on which number I would use to contact these people.

So I’m going to add a comma to add an additional field, and then I’m going to use the COALESCE function. And I’ll put the home phone number field first, so that is the field I want to choose first if they have a value. If that field is null, then I want to use the work phone, and if that field is null, then I want to use the cell phone as first contact.

So now I when I execute this code, you can see it’s combining the fields that if it had a value in the home phone field, then that’s a value I used. But if the home phone is null, then I look for the work phone number. In the case of contact ID number 3, who did not have a home phone number or work phone number, then I use the cell phone number as the first point of contact.

So in this video segment, we’ve discussed how to use the ISNULL and the COALESCE function to work with null values.