Sorting Records in a SELECT statement
Try reloading this page, or reviewing your browser settings
This video segment explains the concept of non-existent values, how to detect them, and filter on missing values in a WHERE clause.
- NOT NULL
About this video
- John Deardurff
- First online
- 23 November 2018
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this video segment, we’re going to talk about how to filter records for the fields with null values. So let’s get to our demonstration.
First of all, what is a null value? A null value is an absence of value, where there’s no value actually in a field. You can see here, I’ve already opened up our SQL server Management Studio. Within our query editor window I’ve selected the FirstName field, LastName field and MiddleName field from the Person.Person table. I’ve executed the query and we have 19,972 people in the Person.Person table. But you can see now that I’ve included MiddleName in this query, that there are some people who do not have a value in their middle name. If we look and see, we have three Kim Ambercrombies, and two of those Kim Ambercrombies do not have a middle name, where one of them does have a middle initial of B.
We can also see Sam Abolrous does not have a middle initial. Gustavo Achong also does not have a middle initial. So that is an example of records, or in this case a field, that has no values, so there’s no value in that field.
But what if I wanted to search or filter out or find those people who do not have a middle name? How would I do that? Well, I would go back, I would include a WHERE statement, so I’ll type in WHERE MiddleName MiddleName equals NULL. So we can type in that code, and when I execute you can see nothing came back. That’s because we cannot equal null. Null has no value. So we couldn’t write the statement where MiddleName equals NULL, because nothing can equal NULL. Nothing can equal an absence of value. So in that case, I have to, instead of saying equals null, I have to type in IS NULL. It is, in fact, an absence of value or it is null. So now when I execute that query, now you can see I only have 8,499 rows, so those are all the people that did not have a middle initial or middle name. And you can see I only have the two Kim Ambercrombies now, and I’m missing the one that had B for her middle initial.
Now if I did want to see the people who did have a value for their middle name, that is where I would use where MiddleName IS NOT NULL, where it’s not empty, where it actually does have a value. So I’m going to type that in, execute, and now you can see we have 11,473 rows, or more specifically the people who did have a middle initial. In this case, you can see the Kim Ambercrombie does have a middle initial. The one Kim that did have a middle initial, she shows up now. The two that did not, they do not show up in this result set.
So in this video segment, we have just seen how to filter records for the fields that have null values.