COALESCE and NULLIF: Two Functions That Clean Up Every Blank Cell

The report you sent your manager shows the word “null” in 40 cells. Two functions fix it without touching the data.

The problem

Blanks scattered across the report. Not corrupted data — just information nobody has yet. But showing NULL looks broken.

COALESCE — the first non-NULL value

SELECT
  name,
  COALESCE(phone, email, 'not provided') AS contact,
  COALESCE(balance, 0)                   AS balance
FROM customers;

It scans left to right and stops at the first value that is not NULL. It accepts any number of arguments.

NULLIF — turning a value into NULL

NULLIF(a, b)   -- returns NULL if a = b, otherwise returns a

Its main use: preventing divide-by-zero.

SELECT ROUND(100.0 * passes / NULLIF(total, 0), 1) AS pass_rate
FROM stats;

A zero denominator becomes NULL, and the division returns NULL instead of blowing up the query.

Its second use: treating empty strings as missing data.

COALESCE(NULLIF(TRIM(notes), ''), 'no notes')

Common mistakes

1. Using ISNULL or IFNULL and then changing database

ISNULL is SQL Server, IFNULL is MySQL, NVL is Oracle. Only COALESCE is ANSI standard and works everywhere.

2. COALESCE discarding the index

-- Full table scan
WHERE COALESCE(status, 'new') = 'new'

-- Index-friendly
WHERE status = 'new' OR status IS NULL

3. Mismatched data types

-- Errors on several engines: mixing number and text
COALESCE(quantity, 'not entered')

Every argument must share a type. Cast the number to text first if you want a label.

Practice

• LeetCode 1179 — Reformat Department Table
• LeetCode 1795 — Rearrange Products Table
• LeetCode 1965 — Employees With Missing Information
• LeetCode 1075 — Project Employees I
• HackerRank — Population Census