When learning SQL, a common mistake when checking for NULL values is to use an equality operator. In SQL, a NULL value is treated a bit differently to other values.

In SQL we use IS NULL instead of = NULL. Likewise, we use IS NOT NULL instead of <> NULL or != NULL to select those columns that don’t have a NULL value.

Example

Suppose we run the following query:

SELECT
    CustomerId,
    CustomerName,
    Email
FROM Customers;

Result:

+------------+--------------------+------------------------+
| CustomerId | CustomerName       | Email                  |
+------------+--------------------+------------------------+
|       1001 | Palm Pantry        | [email protected]   |
|       1002 | Tall Poppy         | NULL                   |
|       1003 | Crazy Critters     | [email protected] |
|       1004 | Oops Media         | [email protected]    |
|       1005 | Strange Names Inc. | NULL                   |
+------------+--------------------+------------------------+
5 rows in set (0.00 sec)

We can see that the Email column contains some NULL values.

Here’s an example of a query that selects only those rows that have NULL in the Email column:

SELECT
    CustomerId,
    CustomerName,
    Email
FROM Customers
WHERE Email IS NULL;

Result:

+------------+--------------------+-------+
| CustomerId | CustomerName       | Email |
+------------+--------------------+-------+
|       1002 | Tall Poppy         | NULL  |
|       1005 | Strange Names Inc. | NULL  |
+------------+--------------------+-------+
2 rows in set (0.00 sec)

Return non-NULL Rows

We can use IS NOT NULL to turn the query around and select only those rows that don’t have a NULL value in the Email column:

SELECT
    CustomerId,
    CustomerName,
    Email
FROM Customers
WHERE Email IS NOT NULL;

Result:

+------------+----------------+------------------------+
| CustomerId | CustomerName   | Email                  |
+------------+----------------+------------------------+
|       1001 | Palm Pantry    | [email protected]   |
|       1003 | Crazy Critters | [email protected] |
|       1004 | Oops Media     | [email protected]    |
+------------+----------------+------------------------+
3 rows in set (0.00 sec)

This time we got the rows that weren’t returned in the previous query.

Explanation

The reason NULL values are treated differently in SQL is because, NULL values are basically unknown values. We can’t compare an unknown value to a known value. Therefore, we can’t say that it equals another value, nor can we say that it doesn’t equal another value – we simply don’t know.

By the same token, we can’t compare an unknown value to another unknown value. We simply don’t know.

Therefore, replacing IS NULL and IS NOT NULL with an equality operator such as = NULL or <> NULL will usually return the wrong result.

Here’s what happens when we use the = operator:

SELECT
    CustomerId,
    CustomerName,
    Email
FROM Customers
WHERE Email = NULL;

Result:

Empty set (0.00 sec)

Likewise with the <> operator:

SELECT
    CustomerId,
    CustomerName,
    Email
FROM Customers
WHERE Email <> NULL;

Result:

Empty set (0.01 sec)

Both queries returned no results. This is because NULL is neither equal to nor not equal to any other value, including NULL itself.

The Exception

As with many things SQL, there can often be exceptions to the rule, and NULL values are no exception!

Some DBMSs allow us to configure the way NULL values are treated. In such cases, we may be able to have NULL equal NULL. In other words, we may be able to change the outcome of the above queries to return a result other than an empty set.

For example, SQL Server has an ANSI_NULLS setting that allows us to change the way NULL values are treated in such cases. See SQL Server ANSI_NULLS Explained for a detailed explanation and examples.