Lessons · SQL · COALESCE

A value instead of NULL

COALESCE(a, b) returns a unless it is NULL, then b.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

SUM over no rows is NULL; a missing field is NULL. Reports need zero and 'n/a', not blanks.

Where can NULL show up in the output? Wherever NULL can appear in output, decide what it should read as and COALESCE it.

SELECT c.name, COALESCE(SUM(o.amount), 0) AS total

Customers with no orders show 0.

FROM customers c LEFT JOIN orders o ON o.customer_id = c.id

LEFT keeps every customer; the ON is where the paid filter would go too.

GROUP BY c.id;

One row per customer.

Show 'unknown' for a missing city.

SELECT (city, 'unknown') FROM customers;

Putting the join's filter in WHERE: WHERE o.status = 'paid' turns the LEFT JOIN back into an inner one. Put it in ON.

Practise COALESCE on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.