SQL Null Functions
COALESCE returns the first non-NULL argument. It is useful for a display fallback or a default in a calculation, provided that the replacement makes sense for the question being asked.
Understand the idea
A missing city is displayed as Not recorded in this report. The stored city remains NULL. COALESCE is widely supported; functions named IFNULL, ISNULL, and NVL have database-specific syntax and type behaviour. Use NULLIF when you need to turn a particular value into NULL, such as a zero denominator before division.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT name, COALESCE(city, 'Not recorded') AS city_label
FROM customers
ORDER BY customer_id;
SELECT 10.0 / NULLIF(0, 0) AS ratio;
Read the result
Noor's city label becomes Not recorded. The second query returns NULL rather than a meaningful ratio because its denominator becomes NULL.
Try one change
Use COALESCE(SUM(total_cents), 0) for a status with no orders. Explain why this report deliberately treats no matching amounts as zero.