COALESCE and NULLIF

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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');
Options
• 'Pyrochu'
• 'Aquachu'
• NULL
• An error: too many arguments

Hint : 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.

Items to match
• COALESCE(a, b, c)
• IFNULL(a, b)
• NULLIF(a, b)
• a IS NULL
Definitions
• The first of the three values that is not NULL
• a, or b when a is NULL
• NULL when a equals b, otherwise a
• A test that is true only when a is NULL

Exercise 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';
Options
• NULL
• 5
• 0
• '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.

Starter code
-- Write your query below
Back to the course