SQL IS NULL and IS NOT NULL

Lesson 2.4 · Reading time: ~6 min · Practice tasks ↓

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: it has 16044 rows, and return_date is NULL for films that haven't been returned yet.

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 rental_id, rental_date, return_date
FROM rental
WHERE return_date = NULL;

Result: no rows, 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 rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NULL;

Result: 183 rows - the rentals whose films are still out.

IS NULL combines with other conditions like any comparison:

SELECT rental_id, rental_date, return_date
FROM rental
WHERE customer_id = 554 AND return_date IS NULL;

Result: one row, rental 14098 of 2005-08-21: customer 554 still has this film.

IS NOT NULL

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

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NOT NULL;

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

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. Compare two queries:

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01';

Result: 8207 rows.

SELECT rental_id, return_date
FROM rental
WHERE NOT (return_date >= '2005-08-01');

Result: 7654 rows. 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 rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Result: 8390 rows = 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 title, language_id
FROM film
WHERE language_id NOT IN (2, 3);

Result: 1000 rows - all the films.

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, NULL);

Result: no rows - language_id <> NULL is UNKNOWN for every film.

Nobody writes NULL in a list by hand, but later in the course the list will come 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')
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21';

Result: 4 rentals; rental 14098 shows "not returned". Without a name, the column is titled with the expression itself.

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; both combine with AND and OR.
• 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.

Can IS NULL be combined with AND and OR?

Yes, like any other condition: WHERE customer_id = 554 AND return_date IS NULL finds the films a customer still has, and OR return_date IS NULL adds the rows without a value to another condition.

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.