Level 12: Handling missing values (NULL)

Lesson

NULL means “unknown value”. It is neither zero nor empty text: it is an absence of information. A contact without a phone does not have the number 0, they have an unknown number.

Since the value is unknown, almost any calculation that touches it gives an unknown result: 5 + NULL is NULL, 'a' || NULL too. A comparison with NULL gives neither true nor false, but unknown: phone = NULL is unknown, and so is NULL = NULL, since two unknown values are not necessarily equal.

SQL therefore reasons with three values: true, false and unknown. Decisive rule: WHERE only keeps the rows where the condition is true, and drops the “unknown” rows just like the “false” ones. That is why WHERE phone <> '0612345678' also drops the contacts without a phone: for them, the test is unknown.

With AND and OR, unknown combines logically: false AND unknown is false, true OR unknown is true, but true AND unknown stays unknown. The NOT IN trap comes from there: x NOT IN (1, NULL) means x <> 1 AND x <> NULL; the second half is always unknown, so the condition is never true.

To test for NULL, you use IS NULL and IS NOT NULL, which always answer true or false.

To replace a missing value, COALESCE(a, b, c) returns the first non-NULL value of the list: COALESCE(phone, 'unknown') shows 'unknown' when the phone is missing.

NULLIF(a, b) does the opposite: it returns NULL if a = b, otherwise a. It is mainly used to protect a division: x / NULLIF(y, 0) gives NULL instead of a division-by-zero error in most engines (SQLite already returns NULL).

Finally, aggregates ignore NULL: COUNT(*) - COUNT(phone) gives the number of missing phones.

Syntax

SELECT COALESCE(col1, col2, 'default'), x / NULLIF(y, 0)
FROM my_table
WHERE col IS NOT NULL;

Worked example

SELECT name, COALESCE(email, 'no email') AS email
FROM contacts;

Contacts without an address show 'no email' instead of an empty cell.

Example result

Key points

• NULL = unknown: test it with IS NULL.
• COALESCE replaces NULL with the first known value.
• NULLIF(y, 0) protects a division.

Common pitfalls

• WHERE phone <> '0612345678' also drops the NULL phone numbers.
• COALESCE must receive values of the same kind: text with text, numbers with numbers.

The level’s 5 exercises

1. Guided · Show the name of each contact and their phone number, showing 'unknown' when it's missing. (Table: contacts)
2. Practice · Show the name of each contact and their best way to be reached: the email if it exists, otherwise the phone, otherwise 'no contact'. (Table: contacts)
3. Practice · For each match, show the id, the goals of both teams and the ratio home goals / away goals, rounded to 2 decimal places (goal_ratio). When the away team did not score, the ratio must be empty (NULL). (Table: matches)
4. Practice · On a single row, show the number of invoices (nb_invoices), the number of unpaid invoices (unpaid) and the total amount still due (amount_due). An unpaid invoice has no payment date. (Table: invoices)
5. Challenge · Show the title of each movie and the name of its director, showing 'Unknown' when it isn't known. Every movie must appear. (Tables: movies, directors)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic