Missing values: NULL

Real data has gaps. A customer skips the "city" box, or a new book hasn't been given a genre yet. The database marks these gaps with NULL, which means "unknown" or "missing".

In this lesson's practice database, two customers have no city and one book has no genre.

NULL isn't zero and it isn't empty text. It's "we don't know". That has a surprising effect: you can't find NULL with =.

-- Returns nothing, even though two cities are missing!
SELECT name FROM customers WHERE city = NULL;

Is an unknown city equal to unknown? The database can't say yes, so the row is left out. Use IS NULL and IS NOT NULL instead:

SELECT name FROM customers WHERE city IS NULL;
SELECT name FROM customers WHERE city IS NOT NULL;

Anything combined with NULL is NULL. NULL + 5 is NULL, because unknown plus five is still unknown.

Fill the gaps in results with COALESCE. It returns the first value that isn't NULL:

SELECT name, COALESCE(city, 'Not given') AS city
FROM customers;

This only changes what you see in the result, not what's stored.