Lesson 2.4 · Reading time: ~6 min

IS NULL and IS NOT NULL are the only reliable way to filter missing values in SQL. In this lesson you'll see why WHERE column = NULL finds nothing, how to find rows with and without a value, and how NULL quietly drops rows from conditions built with NOT, <> and NOT IN. The examples use the rental table of the Sakila database, where return_date is NULL for films that haven't been returned yet.

SQL IS NULL and IS NOT NULL

Why = NULL Finds Nothing

NULL means "the value is unknown". Any comparison with an unknown value is unknown too: return_date = NULL is neither true nor false, it's UNKNOWN. WHERE keeps only the rows where the condition is TRUE, so the query returns nothing:

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date = NULL;

Result: 0, although 183 rentals have no return date.

The same happens with <>, >, < and any other comparison operator.

IS NULL

IS NULL is a special check that is TRUE when the value is missing:

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date IS NULL;

Result: 183.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date
LIMIT 3;

Result: rentals 14098 (customer 554), 13421 (customer 366) and 15542 (customer 111) — the oldest films that are still out.

IS NOT NULL

IS NOT NULL is the opposite: TRUE when the column has a value.

SELECT COUNT(*) AS returned FROM rental WHERE return_date IS NOT NULL;

Result: 15861. Together with the 183 rentals without a return date that makes all 16044 rows.

NULL Falls Out of a Condition and of Its NOT

It's natural to think that a condition and its NOT split a table in two. With NULL they don't:

SELECT
    (SELECT COUNT(*) FROM rental WHERE return_date >= '2005-08-01') AS after_august,
    (SELECT COUNT(*) FROM rental WHERE NOT (return_date >= '2005-08-01')) AS before_august,
    (SELECT COUNT(*) FROM rental) AS total;

Result: 8207 + 7654 = 15861, not 16044. For the 183 rows without a return date both conditions are UNKNOWN, and NOT UNKNOWN is still UNKNOWN.

To keep these rows, ask for them explicitly with OR ... IS NULL:

SELECT COUNT(*) AS late_or_unknown
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Result: 8390 = 8207 + 183.

The NOT IN Trap

NOT IN is a chain of <> comparisons joined by AND. If the list contains NULL, one of them is always UNKNOWN, and no row can pass:

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, 3);

Result: 1000.

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, NULL);

Result: 0 — language_id <> NULL is UNKNOWN for every film.

Nobody writes NULL in a list by hand, but the list often comes from a column, and a single NULL there is enough to empty the result. Before NOT IN, make sure the values can't be NULL.

Showing a Value Instead of NULL

COALESCE returns the first of its arguments that is not NULL. It's handy for showing a readable value in the result:

SELECT rental_id, rental_date, COALESCE(return_date, 'not returned') AS returned_at
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21'
ORDER BY rental_date;

Result: 4 rentals; the first one, 14098, shows "not returned".

This is MySQL. PostgreSQL and SQL Server need both arguments of the same type, so there the date is first converted to text. COALESCE and other functions are covered in the functions module.

Key Takeaways:

• Any comparison with NULL (=, <>, >, <) gives UNKNOWN, and WHERE drops such rows.
• Use IS NULL to find rows without a value and IS NOT NULL to find rows with one.
• A condition and its NOT together don't cover the rows where the column is NULL; add OR column IS NULL to keep them.
• NOT IN returns no rows at all if the list contains NULL.
• COALESCE(column, value) shows a replacement value instead of NULL.

Frequently Asked Questions

Why does WHERE column = NULL return no rows?

A comparison with NULL is UNKNOWN, not TRUE, and WHERE keeps only TRUE rows. Use WHERE column IS NULL.

Is IS NOT NULL the same as NOT (column = NULL)?

No. column = NULL is UNKNOWN, and NOT UNKNOWN is UNKNOWN too, so NOT (column = NULL) also finds nothing. IS NOT NULL is TRUE for every row that has a value.

Does COUNT(column) count NULL values?

No. COUNT(*) counts rows, COUNT(column) counts only the non-NULL values of the column. Aggregate functions are covered in their own module.

Interview Questions

Why do SQL queries use IS NULL instead of = NULL?

NULL means an unknown value, and comparing anything with an unknown value gives UNKNOWN rather than TRUE or FALSE. WHERE keeps only rows where the condition is TRUE, so = NULL never matches. IS NULL is a separate test that returns TRUE or FALSE.

Why can NOT IN return an empty result?

x NOT IN (a, b) means x <> a AND x <> b. If the list contains NULL, x <> NULL is UNKNOWN for every row, the whole AND can't be TRUE, and no rows are returned. This happens when the list comes from a subquery whose column has NULL values.

How do you keep rows with NULL in a filter?

Add the NULL case explicitly: WHERE return_date >= '2005-08-01' OR return_date IS NULL. Otherwise the rows with NULL are dropped by both the condition and its negation.