sql · level 2 · unit 5 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
swaps a NULL for a .
Reports with NULL in them look unfinished. COALESCE(author, 'Unknown') looks at the author. If there is one, you get it. If it is NULL, you get the fallback instead.
It hands back the first value in its brackets that is not NULL, so you can list several, like COALESCE(nickname, name, 'friend').
It fixes the SUM problem too. After a LEFT JOIN, COALESCE(SUM(o.quantity), 0) turns an empty group's NULL into 0.
SELECT title, COALESCE(author, 'Unknown') AS author,
COALESCE(borrower, 'on the shelf') AS borrower
FROM books
ORDER BY title;Pick a fallback of the same kind: text for a text column, a number for a number column.
guess first
Items per customer, Sam included. What does Sam get?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT c.name, COALESCE(SUM(o.quantity), 0) AS items
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY items DESC, c.name;your turn
Your turn. Fill the gaps in the books.
Type a fallback between the quotes, like nobody or free to borrow. Books with no borrower show it instead of NULL.
result
Your answer appears here as a table.
fix it
Every author says Unknown. Fix it!
result
Your answer appears here as a table.
COALESCEhands back the first value that is notNULL. What comes first here?- 'Unknown' is never
NULL, so it wins every time. The real author should be checked first. - Swap them to COALESCE(author, 'Unknown'), then press run.
nailed it
no more blanks in your reports. :D
COALESCEgives the first value that is notNULL.- Put the real column first and the fallback last.
COALESCE(SUM(...), 0)turns an empty sum into 0.
new words in your collection
- COALESCE
- fallback
This lesson isn’t saved. A free account remembers every lesson you finish.