COALESCE and NULLIF
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — NULL spreads
Remember Shadowchu's missing power? NULL does not just sit there quietly: any calculation involving NULL gives NULL.
SELECT name, power, power + 10 AS boosted
FROM codemon;COALESCE returns the first non-NULL value among its arguments. Give it a fallback and the NULL disappears:
SELECT name, power,
COALESCE(power, 0) AS safe_power,
COALESCE(power, 0) + 10 AS boosted
FROM codemon;As many arguments as you like
COALESCE(a, b, c, ...) checks the values from left to right and returns the first one that is not NULL. If they are all NULL, the result is NULL.
Exercise 2 — Fallback values for display
COALESCE is not only for numbers. Most Codémons have no nickname — let's show the nickname when there is one, and the name otherwise:
SELECT name, nickname,
COALESCE(nickname, name) AS display_name
FROM codemon;You can also replace the NULL with a message:
SELECT name,
COALESCE(nickname, '(no nickname)') AS nick
FROM codemon;IFNULL, the two-argument cousin
SQLite and MySQL also offer IFNULL(a, b), which is exactly COALESCE with two arguments. COALESCE is standard SQL and works in every database, so prefer it.
Exercise 3 — First non-NULL
What does this query return?
SELECT COALESCE(NULL, NULL, 'Pyrochu', 'Aquachu');'Pyrochu''Aquachu'NULLHint : COALESCE stops at the first value that is not NULL.
Exercise 4 — A safer average
AVG(power) ignores Shadowchu because its power is NULL. Complete the query so that the missing power counts as 0 in the average.
Hint : Replace the NULL first, then average the result.
Exercise 5 — NULLIF: turning a value into NULL
NULLIF(a, b) does the opposite job: it returns NULL when a equals b, and a otherwise.
SELECT NULLIF(5, 5) AS same,
NULLIF(5, 3) AS different;Its classic use is to avoid dividing by zero. Pyrochu has 0 losses, so its win/loss ratio would divide by zero. With NULLIF the divisor becomes NULL, and so does the result — no crash, just an honest "unknown":
SELECT name, wins, losses,
ROUND(wins * 1.0 / NULLIF(losses, 0), 2) AS ratio
FROM codemon;SQLite is forgiving, others are not
In SQLite a division by zero quietly returns NULL. Most other databases (PostgreSQL, SQL Server...) raise an error and stop the whole query. NULLIF makes your intent explicit and your query portable.
Exercise 6 — Who does what
Match each expression with what it returns.
COALESCE(a, b, c)IFNULL(a, b)NULLIF(a, b)a IS NULLa, or b when a is NULLa equals b, otherwise aa is NULLExercise 7 — NULLIF in action
Florachu has 5 wins and 5 losses. What does this query return?
SELECT NULLIF(wins, losses)
FROM codemon
WHERE name = 'Florachu';NULL50'Florachu'Hint : Compare Florachu's wins and losses: are they equal?
Exercise 8 — Both at once
Complete the query so that it shows the nickname (or the name when there is no nickname) and the win/loss ratio, without ever dividing by zero.
Hint : One function picks the first non-NULL value, the other turns a dangerous value into NULL.
Exercise 9 — A clean battle sheet
The battle sheet is full of holes. Professor Pi wants a clean version.
Write a query that returns:
1. display_name: the nickname when there is one, otherwise the name
2. safe_power: the power, or 0 when it is unknown
3. ratio: wins divided by losses with 2 decimals, and NULL when there are no losses
Sort the result by display_name.
-- Write your query below