Querying Where Null in SQL

A particularly confusing aspect of writing queries is querying where NULL in SQL. What does that mean exactly?

Null is a special type of operator in SQL. It is essentially the absence of a value. As such, we have to handle queries against columns with potentially null values in a specific way.

Table of Contents

Using Nullable Columns in SQL

When creating your tables and adding columns, you have the option for most columns to allow NULL in the column. This means that the database doesn’t require you to specify a value. You can simply leave that column empty for any given row in the table.

Sometimes it makes sense to allow nulls in a column. You may not always need or have a value for a given field in a particular record. Instead of forcing an incorrect value or a value that doesn’t make sense, you can simply leave it null.

Whenever possible, you should avoid allowing NULL values in a column, though. The reason is that it can cause complications or unexpected results with your data and your queries. Using aggregate functions, such as using SUM in your SQL queries, will exclude NULL results and this can potentially cause some issues such as when you’re performing aggregates on multiple columns.

Setting Up Our Data for Querying Where Null in SQL

Let’s start by creating a table that has a few columns that allow NULL values and one column that doesn’t. We’ll have columns of varchar and int data types so we can see that we can put NULL in a variety of different data typed columns.

CREATE TABLE product_details (
    product_name varchar(50) not null,
    product_description varchar(255) null,
    width_inches int null,
    height_inches int null
);

Now, let’s populate some data to use in our queries. We’ll include columns that are NULL as well as columns with values so that we can see the difference.

INSERT INTO product_details (product_name) VALUES ('The Office DVD Box Set');
INSERT INTO product_details (product_name, product_description, width_inches) VALUES ('Yard Stick', '', 0);
INSERT INTO product_details (product_name, product_description, width_inches, height_inches) VALUES ('Monopoly Board Game', 'The greatest board game ever', 18, 9);

Note that we have to put something in the product_name field because in our CREATE TABLE statement above, we specified not null for that column. Also note that we’re inserting an empty string ('') for the first record’s product_description field. We’re going to see that an empty string is not the same as NULL. Let’s run the first query:

SELECT *
FROM product_details
WHERE product_description = NULL;

What results do you think we’ll get here? In our insert statements, we didn’t populate the product_description in the first record. That means it will default to NULL. But wait! There are no results from this query! Why is that? Well, it’s because we’re using the wrong operator for our NULL comparison. Using “equals” on NULL won’t return anything because nothing is equal to NULL. Instead, we have to use the “IS” operator, like this:

SELECT *
FROM product_details
WHERE product_description IS NULL;

This will give us the first record – “The Office DVD Box Set”. We didn’t populate the product_description in our INSERT statement, so the database put NULL in it.

Null Versus Empty Strings

It’s important to know that NULL is not the same as an empty string either. This can be a common mistake. It’s not uncommon even for seasoned database developers to write a query like this:

SELECT *
FROM product_details
WHERE product_description = '';

This query will run just fine. But will it give you the results you want? If you want any product that doesn’t have a description, then you’re going to be missing a record. The first record has NULL for the product_description so it’s not included.

To get the actual results we want, we would have to run a query that looks like this:

SELECT *
FROM product_details
WHERE (product_description = '' OR product_description IS NULL);

In this case, we’re going to get two results (‘The Office DVD Box Set’ and ‘Yard Stick’). Just remember as you’re formulating your queries that you need to take into account any potentially NULL columns in order to get the expected results.

Null Values in Numeric Columns

As we mentioned before, you can have NULL values in a variety of different data typed columns. That includes numeric values. In some cases, for example in foreign key relationships, it makes sense to allow numbers to be nullable. This might indicate that there is no relationship assigned yet.

However, in other cases, it can cause problems. For example, in this query we’re counting the number of records and also calculating the total square inches of all products. (Yes, this is a weird query that you probably wouldn’t run in the real world, but it illustrates the problem.)

SELECT SUM(width_inches * height_inches), COUNT(*)
FROM product_details;

Remember that we have one record with NULL for width_inches and two records with NULL for height_inches. So the only value we’re getting here for our SUM function is the one record that has both values. The other two records are excluded. However, the COUNT function is returning a count of all the records. If you made the assumption here that the average square inches per record is 162 / 3 then that could be a false conclusion. You don’t have all the data for two of those records!

Null in SQL Queries

Querying where NULL in SQL can be tricky to get the handle of at first. In fact, it even trips up experience SQL coders sometimes too. Just remember that if you have nullable columns in your table, you have to be a little more careful and intentional about how you pull the data out.

We cover NULL in WHERE clauses in a good amount of detail in our online Introduction to SQL course. If you want to deep dive into the SQL basics, you should take the course. You’ll be a pro by the weekend!