Missing Values: NULL

You know NULL from the first lesson: it means that a value is missing. The shop doesn't know Ava Green's city, and Robot Rebellion has no release date yet. To find such rows, it seems natural to write = NULL. But this query finds nothing:

SELECT name, city
FROM customers
WHERE city = NULL;

That's not a mistake in the data. NULL means “unknown”, and comparing anything with an unknown value gives an unknown result. Is London equal to some unknown city? Nobody knows. Such a comparison is neither true nor false; its result is NULL again. And WHERE keeps only the rows for which the condition is true. Not even NULL = NULL is true: are two unknown cities the same? Unknown again. You can try it yourself: a SELECT without FROM reads no table, it just works out the values you write and shows them as one row:

SELECT 5 = NULL, NULL = NULL;

That's why SQL has a special operator for missing values, IS NULL. The games that aren't out yet:

SELECT title, released
FROM games
WHERE released IS NULL
ORDER BY title;

Its opposite is IS NOT NULL, the rows that do have a value: WHERE released IS NOT NULL finds the 18 games that are out.

The same goes for every other comparison, <> included. Which customers aren't from London?

SELECT name, city
FROM customers
WHERE city <> 'London'
ORDER BY name;

Ava Green and Henry Scott aren't in the result. Is an unknown city different from London? Unknown again. If you want them too, you have to ask for them: city <> 'London' OR city IS NULL.

Typical mistake: = NULL instead of IS NULL. It isn't an error, so SQLite won't warn you; the query just finds nothing. Whenever you look for missing values, write IS NULL.