COALESCE and NULLIF

Substituting values for nulls, and generating them deliberately.

What you'll be able to do

Replace nulls with a fallback
Use NULLIF to guard division
Choose between COALESCE and dialect-specific alternatives

COALESCE

Returns the first non-null argument:

COALESCE(nickname, first_name, 'Unknown')
COALESCE(discount, 0)

It takes any number of arguments and is standard SQL. IFNULL (MySQL) and ISNULL (SQL Server) do the same for two arguments only — prefer COALESCE for portability.

Where nulls break arithmetic

Any arithmetic involving null produces null:

price - discount        -- null whenever discount is null
price - COALESCE(discount, 0)   -- correct

This quietly wipes out values in a calculated column, and because null displays as blank rather than as an error it is easy to miss.

NULLIF

Returns null when the two arguments are equal, otherwise the first:

NULLIF(order_count, 0)

The main use is guarding division. It is also handy for treating a placeholder as missing: NULLIF(city, 'N/A').

Combining them

COALESCE(ROUND(100.0 * shipped / NULLIF(total, 0), 1), 0) AS pct

NULLIF prevents the error, COALESCE turns the resulting null into a displayed zero.

Key points

COALESCE returns the first non-null argument and is portable
Any arithmetic with null yields null — wrap nullable columns
NULLIF(x, 0) is the standard division-by-zero guard

Check yourself