The IS NULL predicate is a very useful thing to know when it comes to querying data and working with columns containing NULL.
In this very brief tutorial, we’ll talk about why you need to use IS NULL instead of something like “= NULL” when checking if a value contains NULL.
Don’t forget to download your FREE 1-PAGE Simple SQL Cheat Sheet on SQL Server NULL!
This guide contains all the key points you need to know about NULL condensed into a simple 1-page guide. It will be an excellent resource for you to reference during your career as a database professional. Make sure you download it today!
Without further ado…
Why can’t I just use “= NULL”?
Here’s the thing about NULL: Comparing anything to NULL results in the logical value unknown, because NULL represents the absence of information.
We’ll take a very simple example. Take a look at this ContactInfo table where we see that some rows contain NULL in the Addr column:
What if we wanted to write a query that specifically returns rows where the Addr value is NULL? Let’s try using “= NULL”:
We get an empty result set.
Folks, a WHERE clause will return rows where the predicate evaluates to True. Remember, a comparison again NULL will evaluate to unknown. This is why nothing at all get’s returned when we use “= NULL“. Everything evaluates to something other than true.
“What about NULL = NULL?”
Surely “NULL = NULL” would evaluate to true, right? WRONG. If we compare unknown to unknown, the result is still unknown.
As a silly analogy, think about two people’s middle names. Do you know Anne Hathaway’s middle name? NOPE (don’t Google it either you cheater).
Do you know my middle name? NOPE.
Therefore, can you say with certainty that our middle names are equal?
Well, no. I mean, I suppose they could be. But the truth is you simply don’t know. This is why when we compare one unknown value to another unknown value, the result is that we still simply don’t know if they are equal!
Ergo IS NULL
This is exactly why we need the IS NULL predicate. This is the tool we use to see if a column specifically contains NULL as it’s value.
So if we wanted to see customers who have NULL in their Addr column, we would need to use IS NULL:
Using IS NULL in a LEFT JOIN
As a final example, let’s think about how we can utilize IS NULL in a LEFT JOIN operation.
Take a look at the following Customers and ContactInfo tables:
Notice the CustID column in the ContactInfo table is basically a foreign key link back to the Customers table.
Let’s say we want to write a query that pulls details for all customers in the Customers table, and see what their ContactID value is over in the ContactInfo table.
This would involve a JOIN between the Customers and ContactInfo table. Since I said we want to see all customers (regardless of if there is a corresponding row in the ContactInfo table), this would mean we want to do a LEFT JOIN:
Notice the last two rows for Doug and Mary Jones. They simply don’t have a record in the ContactInfo table yet. Maybe they are new customers and we simply haven’t had the time to enter their contact information yet. Therefore, NULL is used as the ContactID value for these two people.
(Remember, NULL represents the absence of information, which is exactly what we have here)
Ok, what if we wanted to know what customers specifically don’t have a record in the ContactInfo table? We’ll, we could filter our query to return rows where the ContactID column is specifically NULL.
Maybe like this?
NO!!! We already know that won’t work.
This is a perfect place to use the SQL Server IS NULL predicate:
So again, this tells us specifically which customers haven’t been set up in the ContactInfo table.
Great success!
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE 1-PAGE Simple SQL Cheat Sheet on SQL Server NULL!
The IS NULL predicate is part of this guide, as well as many other tools you can use when working with NULL in SQL Server. It will be an excellent resource for you to reference during your career as a database professional. Make sure you download it today!
Take a look at the full tutorial on NULL here:
SQL Server NULL: Are you making these 7 mistakes?
By the time you finish reading that tutorial, you will be a NULL master!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
