IS NULL / IS NOT NULL

NULL represents missing or unknown data in SQL. Because of how NULL behaves in comparisons, you cannot test for it the way you would test for an ordinary value — you need the dedicated IS NULL and IS NOT NULL operators.

Never use = NULL or != NULL

In SQL, NULL means “unknown”, and any comparison against an unknown value is itself unknown — not true, not false. That means column = NULL never evaluates to true, even for rows where column genuinely is NULL. A WHERE column = NULL condition silently matches nothing, which is a very easy mistake to make. See the null-handling section of this series for a deeper look at three-valued logic.

Checking for missing data

Correct way to find NULL values

SQL

SELECT first_name, last_name, phone_number
FROM customers
WHERE phone_number IS NULL;

This correctly returns every customer with no phone number on file — something WHERE phone_number = NULL would fail to do.

Checking for present data

Finding rows where data IS present

SQL

SELECT first_name, last_name, phone_number
FROM customers
WHERE phone_number IS NOT NULL;

A worked example

Suppose you want a report of customers who signed up but never completed their profile — meaning their address column was never filled in:

Finding incomplete profiles

SQL

SELECT id, first_name, last_name
FROM customers
WHERE address IS NULL
ORDER BY id;

Combine IS NULL / IS NOT NULL with AND or OR just like any other condition, for example to find customers missing *either* a phone number or an address.