SQL NULL Values

NULL is a marker for a missing value. A missing value is not zero, and it is not empty text.

Equal to NULL finds nothing

This query tests city with = NULL. The result holds no rows, so only the header line comes back.

SELECT name, city FROM customers WHERE city = NULL ORDER BY name;

Find the missing value

IS NULL tests for a missing value. Hana Sato is the one customer with no city stored.

SELECT name, city FROM customers WHERE city IS NULL ORDER BY name;

Find the present values

IS NOT NULL keeps rows that hold a value. Seven customers have a city stored.

SELECT name, city FROM customers WHERE city IS NOT NULL ORDER BY name;

Example

SELECT name, city, country FROM customers WHERE city IS NULL AND country = 'Japan' ORDER BY name;

Key points

NULL: the marker stored when a value is missing
Use IS NULL to find rows where a value is missing.
Use IS NOT NULL to find rows where a value is present.
A test that sets a column equal to NULL matches no rows.
A missing value prints as NULL in a result set.

In this example, IS NULL finds the row with a missing city. The AND test also checks that the country is Japan. Hana Sato passes both tests, and her missing city prints as NULL.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

SELECT name, city, country FROM customers WHERE city IS NULL AND country = 'Japan' ORDER BY name;

Exercise

Which test finds customers with a missing city?

city IS NULL city = NULL city = 'NULL' city IS NOT NULL