Replacing NULL with COALESCE

You know that NULL means no value. That has a consequence that's easy to forget: a calculation with NULL gives NULL. Five plus something unknown is unknown, and so is a text joined with nothing. That's a problem in this list of customers, as Ava Green's city is NULL:

SELECT name, 'Lives in ' || city AS info
FROM customers
WHERE id BETWEEN 4 AND 6;

Not just the city is gone, but the whole text: Ava Green's info is NULL. The same happens with +, -, * and /, and most functions give NULL for NULL, as you saw with LENGTH.

The function COALESCE fixes that. It takes any number of arguments and gives back the first one that isn't NULL. So COALESCE(city, 'an unknown city') is the city when the customer has one, and the text 'an unknown city' when they don't:

SELECT name, 'Lives in ' || COALESCE(city, 'an unknown city') AS info
FROM customers
WHERE id BETWEEN 4 AND 6;

With more arguments, COALESCE tries them one after another: COALESCE(a, b, 'none') is a if a has a value, otherwise b if b has one, and otherwise 'none'.

SQLite also has IFNULL(x, y), which does the same with exactly two arguments. COALESCE is part of standard SQL and works in every database, so this course uses it. You could also write the same with CASE WHEN city IS NULL THEN … ELSE city END, but COALESCE is much shorter.

Replace NULL only in what a query shows. In the table, NULL says something true, that the shop doesn't know the city, and an invented value would hide that. And make sure the replacement makes sense: a missing number isn't always 0.

Typical mistake: a replacement text without quotes. COALESCE(city, unknown) looks for a column called unknown and fails with no such column: unknown. A text always goes in single quotes: COALESCE(city, 'unknown').