Unit 03.03: COALESCE: when zero is a decision, not a default

Unit ID: SQL-M03-U04 - Estimated active time: 12-15 minutes Objective: choose between excluding and substituting a missing value, and justify the choice.

COALESCE returns the first non-NULL argument

SELECT COALESCE(score, 0) FROM tests;

This is easy to reach for and easy to misuse, because it changes the answer:

SELECT ROUND(AVG(score), 2)                AS skip_nulls,
       ROUND(AVG(COALESCE(score, 0)), 2)   AS nulls_as_zero
FROM tests;
-- 76.25 | 61.0

Same column, same ten rows, 15 points apart. Neither is a bug. They answer different questions.

The question to ask

Does a missing value mean the thing did not happen, or that it was not recorded?

• A learner who sat the test and scored nothing → zero is defensible.
• A learner who never sat the test → zero invents a failing result that did not occur.

In our tests table the NULLs are unrecorded, not zeroes, so AVG(score) at 76.25 is the honest figure - provided you also state that it covers 8 of 10.

Where COALESCE is clearly right

Display and counting, where NULL would break the output rather than the meaning:

SELECT
  COALESCE(country, 'Unknown') AS country,
  COUNT(*)                     AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;
-- IN      | 2813
-- GB      |  902
-- AE      |  516
-- Unknown |  300
-- SG      |  281

Here it makes the unknown group visible and countable. It changes the label, not the arithmetic - which is the safe use of the function.

Non-example

SELECT AVG(COALESCE(rating, 3)) FROM feedback;

Substituting the midpoint looks neutral and is not: it invents 60 opinions and pulls the average toward your assumption. If you must fill, say so in the evidence note and show the unfilled figure alongside.

Practice

You must report a single "average rating" for the 500 feedback rows. Give the figure you would publish, and one sentence a reviewer would need.

SELECT ROUND(AVG(rating), 2) AS avg_rating,
       COUNT(rating)         AS ratings,
       COUNT(*)              AS responses
FROM feedback;
-- 3.05 | 440 | 500

Takeaway

COALESCE for labels, rarely for measures. Substituting a value into an average is a claim about data you do not have, and it belongs in the evidence note if you do it at all.

---

Mark complete