COALESCE and NULLIF in SQL

COALESCE(a, b, ...) returns the first non-NULL argument — use it to substitute defaults for NULLs. NULLIF(a, b) returns NULL when the two arguments are equal — its killer use is turning a zero divisor into NULL so division can't error.

How Do You Replace NULL With a Default Value?

Wrap the column in COALESCE with the fallback as the second argument. It's standard SQL, unlike the vendor-specific ISNULL/NVL/IFNULL.

SELECT
    name,
    COALESCE(nickname, name)      AS display_name,
    COALESCE(discount, 0)         AS discount
FROM customers;
-- NULL nickname -> falls back to name
-- NULL discount -> 0

Why NULLs need this treatment at all is covered in NULL handling in SQL.

How Does NULLIF Prevent Divide-by-Zero?

NULLIF(divisor, 0) converts a zero divisor to NULL, and dividing by NULL yields NULL instead of an error. Add COALESCE outside if you want a concrete number back.

-- Errors when clicks = 0:
SELECT conversions / clicks AS cvr FROM ads;

-- Safe: returns NULL for zero-click rows
SELECT conversions / NULLIF(clicks, 0) AS cvr FROM ads;

-- Safe with a default:
SELECT COALESCE(conversions / NULLIF(clicks, 0), 0) AS cvr FROM ads;

How Do You Chain Fallbacks With COALESCE?

COALESCE takes any number of arguments and returns the first non-NULL one, which makes it a clean priority list for contact info, prices, or config overrides.

SELECT
    COALESCE(mobile_phone, work_phone, home_phone, 'no phone') AS best_phone,
    COALESCE(sale_price, list_price, msrp) AS effective_price
FROM contacts;
-- Checks left to right, stops at the first non-NULL

It's shorthand for a CASE WHEN chain of IS NOT NULL tests, and every database supports it.

Common Pitfalls

• Type mismatches: all COALESCE arguments must be compatible types — COALESCE(amount, 'none') fails or forces a cast on numeric columns.
• NULLIF argument order: it's NULLIF(value, match); NULLIF(0, clicks) does not guard the division.
• COALESCE hides data bugs: defaulting NULLs to 0 in reports can mask a broken pipeline. Count the NULLs before papering over them.
• Aggregates already skip NULLs: AVG(COALESCE(x, 0)) and AVG(x) give different answers — the first counts NULL rows as zeros. Pick deliberately.