NULL Value Handling with IS NULL and IS NOT NULL

What Does NULL Really Mean?

First, it's crucial to understand what NULL is—and what it isn't. NULL does not mean zero (0), an empty text string (''), or a string containing a space (' '). NULL is a special marker used in SQL to signify that a value is unknown, missing, or not applicable.

Think of it like this:

• If a user's age is 0, we know their age. They are a newborn.
• If a user's name is '' (an empty string), we know their name. It's just an empty name.
• If a user's age is NULL, we have no information about their age. It is unknown.

This distinction is fundamental. The following video explains the differences visually and conceptually.

The Logic of the Unknown: Why column = NULL Fails

Now for the "why". A common mistake for beginners is to try filtering for NULL values using the equality operator, like this:

-- This query will NOT work as expected!
SELECT *
FROM customers
WHERE phone_number = NULL;

This query will likely return no rows, even if there are customers with NULL phone numbers. To understand why, we need to touch on a core concept in database theory: three-valued logic (3VL).

In standard boolean logic (like in the C language), a comparison is either TRUE or FALSE. SQL, because of NULL, operates on a three-valued logic system:

1. TRUE
2. FALSE
3. UNKNOWN

When you compare something to NULL, the database engine can't definitively say the result is TRUE or FALSE. Is the unknown value of phone_number equal to the unknown value of NULL? Maybe, maybe not. The result of this uncertainty is UNKNOWN. Even the comparison NULL = NULL results in UNKNOWN, because the two unknown values are not necessarily the same.

The WHERE clause in a SQL query only includes rows for which the condition evaluates to TRUE. Rows that evaluate to FALSE or UNKNOWN are both discarded.

This is a subtle but powerful concept. The following article explains it brilliantly.

The Solution: IS NULL and IS NOT NULL

Since standard comparison operators don't work, SQL provides a special syntax for this exact purpose: IS NULL and IS NOT NULL.

These operators are designed specifically to test whether a value is NULL. They are the correct and only reliable way to filter for missing or existing data. Unlike =, >, or !=, these predicates always return TRUE or FALSE.

• WHERE column_name IS NULL: Finds all rows where the value is NULL.
• WHERE column_name IS NOT NULL: Finds all rows where the value is anything other than NULL.

Let's see this in action.

As you saw in the video, the syntax is straightforward.

To find all customers who do not have a score:

SELECT *
FROM sales.customers
WHERE score IS NULL;

To find all customers who do have a score:

SELECT *
FROM sales.customers
WHERE score IS NOT NULL;

SELECT *
FROM service_tickets
WHERE closed_at IS NULL;

An Important Pitfall: NOT IN with NULL

Understanding three-valued logic helps you avoid subtle but severe bugs. A classic example is using NOT IN with a subquery or list that might contain a NULL value.

Consider this query:

SELECT *
FROM customers
WHERE city NOT IN ('London', 'Paris', NULL);

You might expect this to return all customers whose city is not London or Paris. It will return nothing.

Here's why, broken down for a customer in 'New York':

1. The database checks 'New York' NOT IN ('London', 'Paris', NULL).
2. This is equivalent to: 'New York' != 'London' (TRUE) AND 'New York' != 'Paris' (TRUE) AND 'New York' != NULL (UNKNOWN).
3. The final result is TRUE AND TRUE AND UNKNOWN, which evaluates to UNKNOWN.
4. Since the WHERE clause requires TRUE, the row for 'New York' is rejected.

This happens for every row, so the query returns an empty set. This is a logical puzzle that stumps even experienced developers. Always be cautious when using NOT IN with a column or list that could contain NULLs.

Conclusion

Excellent! You've now learned one of the most fundamental and frequently misunderstood concepts in SQL. Mastering NULL is a huge step toward writing robust, bug-free queries.

Key Takeaways:

• NULL represents an unknown or missing value; it is not 0 or an empty string.
• Because of Three-Valued Logic (3VL), standard comparisons with NULL (e.g., column = NULL or column != NULL) result in UNKNOWN.
• A WHERE clause only keeps rows where the condition is TRUE, discarding both FALSE and UNKNOWN results.
• To correctly filter for NULL values, you must use the special predicates IS NULL and IS NOT NULL.

Preview of the Next Lesson:
Now that you can select and filter data, the next step is to control the presentation of that data. In the next lesson, we will learn how to sort your query results using ORDER BY. We will also see how NULL values are handled during sorting, building directly on what you've learned today.