COALESCE for NULLs

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 3 of 27.

COALESCE returns the first argument that is not NULL:

COALESCE(nickname, first_name, 'Anonymous')

Reads left to right and stops at the first non-null. It's the cleanest way to supply a fallback value in a query, with no CASE needed.

Common uses:

Show 0 instead of NULL from a LEFT JOIN: COALESCE(SUM(amount), 0)
Pick the first non-empty optional field: COALESCE(mobile, home, work)

Challenge

Easy

Available tables and columns:
users: id, display_name, email
orders: user_id, amount

Return one row per user with:

id
label: display_name if set, else email, else 'unknown'
total_spent: sum of the user's order amounts; users with no orders should show 0 (not NULL)

Order by id. Hint: a LEFT JOIN from users to orders plus COALESCE(SUM(...), 0) handles the missing rows.

Cheat sheet

COALESCE returns the first non-NULL argument:

COALESCE(nickname, first_name, 'Anonymous')

Common uses:

Fallback display value: COALESCE(mobile, home, work)
Replace NULL from LEFT JOIN aggregates: COALESCE(SUM(amount), 0)

Try it yourself

SELECT u.id,
       -- label via COALESCE
       -- total_spent via COALESCE(SUM(...), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY u.id