NULL: no value

Lesson 5 of 21 · Part 1 · Querying data · about 15 minutes

You met it in lesson 1: some customers left no email, and that cell shows NULL.

1SELECT id, name, email2FROM customers;

NULL means "no value" or "unknown". It isn't 0, and it isn't the empty text '' either: 0 is a number and '' is a text of length 0, while NULL is nothing at all. This lesson is about how to deal with it.

Finding NULL: IS NULL

To find the customers with no email, write IS NULL:

1SELECT name, email2FROM customers3WHERE email IS NULL;

The other way around, IS NOT NULL finds the ones with a value. In the employees table, the employees who have a manager:

1SELECT name, manager_id2FROM employees3WHERE manager_id IS NOT NULL;

Employee 1, the manager, has no manager of her own: her manager_id is NULL, so she isn't in the result.

= NULL finds nothing

This is the trap beginners fall into most.

1SELECT name2FROM customers3WHERE email = NULL;

Run it and see:

1SELECT name2FROM customers3WHERE email = NULL;

Why? Compare a value with "unknown" and the answer is "unknown" too, not "true". WHERE keeps only the rows where the condition is true, so every "unknown" row is dropped. Even NULL = NULL is "unknown": two unknown things can't be said to be equal.

Not equal skips NULL too

For the same reason, <> doesn't pick the NULL rows either. How many customers have an email that isn't [email protected]?

1SELECT name, email2FROM customers3WHERE email <> '[email protected]';

10 customers minus Ana leaves only 6 rows, not 9: the 3 customers with no email are gone too. A comparison with NULL isn't true, and "not equal" isn't either. To count them in, say so:

1SELECT name, email2FROM customers3WHERE email <> '[email protected]' OR email IS NULL;

NULL in math

SELECT also works without FROM, to work out a few values directly. Math with NULL gives NULL, and so does a comparison with it ("unknown"):

1SELECT 10 + NULL, NULL * 2, NULL = NULL, NULL IS NULL;

Only IS NULL gives a definite answer: 1 means true (MySQL shows true as 1 and false as 0).

Something else instead: COALESCE

When showing a result, you often want NULL replaced by something readable. COALESCE(a, b) uses a if it isn't NULL, and b if it is:

1SELECT name, COALESCE(email, 'no email') AS email2FROM customers;

AS email names this column; otherwise its name would be the whole COALESCE(email, 'no email'). The next lesson covers AS.

COALESCE can take several values and uses the first one that isn't NULL. MySQL also has IFNULL(a, b), which takes just two; COALESCE is the standard way and works in every database.

Where NULL sorts

When you sort by a column with NULLs, MySQL treats NULL as the smallest value: first in ascending order, last in descending:

1SELECT name, email2FROM customers3ORDER BY email, id;

The 3 NULL emails are all the same, so sorting them by id as well is what fixes their order. Other databases may do the opposite (PostgreSQL and Oracle put NULL last in ascending order).

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT name, manager_id2FROM employees3WHERE manager_id <> NULL4ORDER BY id;

1SELECT name, email2FROM customers3WHERE city = 'London';

1SELECT name2FROM customers3WHERE email <> '[email protected]';

What you learned

• NULL is "no value": not 0 and not empty text.
• Find it with IS NULL and IS NOT NULL; = NULL and <> NULL find no rows, and give no error.
• Comparing or calculating with NULL gives NULL; WHERE keeps only rows where the condition is true, so <> misses NULL rows too.
• COALESCE(a, b) replaces NULL with something else; MySQL's IFNULL works too, but COALESCE works everywhere.
• In MySQL, NULL sorts as the smallest value; other databases may put it last.