Lesson 8 / 37 · Conditions and NULL

COALESCE gives NULL a default

The usual fix for NULL: coalesce(amount, 0) returns the first argument that is not NULL. Used in WHERE it turns an unknown comparison into a definite one, which changes which rows survive. Arithmetic on NULL stays NULL unless you do this.

The query

SELECT id, amount, coalesce(amount, 0) AS amount_or_zero
FROM orders
WHERE coalesce(amount, 0) <= 10

Try WHERE amount <= 10 without coalesce: order 108 drops out, and 2 rows remain.

Next: CASE picks the first branch that is TRUE · All lessons