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.
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
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
width_inches and two records with
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.
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!