05 DISTINCT, and the trouble with NULL

5 / 15

Goal

List each city customers live in, once each, leaving out customers with no city.

SELECT DISTINCT removes duplicate rows from the result: three customers in London become one "London".

One customer has no city at all. That's NULL — not an empty string, not zero, but "unknown". And NULL is strange: city = NULL is never true, not even for the NULL row, because "unknown equals unknown" is itself unknown. You must ask city IS NULL or city IS NOT NULL.

Try it

Run it without the WHERE first, and spot the NULL in the result.

Go to the terminal

Hint

SELECT DISTINCT city FROM customers WHERE city IS NOT NULL; — = NULL never matches anything.

Show a solution

SELECT DISTINCT city FROM customers WHERE city IS NOT NULL;

This interactive environment needs JavaScript. Everything you need to read is on this page, and the solution works the same way for real.

SQL

Starting SQL…