COALESCE and NULLIF in Practice

COALESCE takes two or more expressions and returns the first one that is not NULL. It is the standard way to substitute a default for a missing value. NULLIF does the opposite: it takes two expressions and returns NULL if they are equal, otherwise it returns the first one. It is the standard way to turn a sentinel value into a real missing value.

They are often used together. NULLIF(COALESCE(raw_value, 0), 0) reads as "treat a missing value and an explicit zero as the same absence of a value", which is what you want when the upstream system uses 0 as its placeholder for unknown.

The caution is about what COALESCE hides. Replacing a missing value with 0 in a query makes the report look complete while quietly changing the meaning of the number. If a customer has no orders and you coalesce the order count to 0, the report says "this customer ordered nothing", which is different from "we do not know how many orders this customer made". If the difference matters, keep the NULL and handle it in the presentation layer, or emit both the raw and the coalesced value.

NULLIF has its own sharp edge. It is not a general comparison helper: NULLIF(a, b) returns NULL whenever a and b are equal, so it is the wrong tool for comparing two columns. If you want null-safe equality, use a IS b in SQLite and PostgreSQL or the explicit (a = b) OR (a IS NULL AND b IS NULL) form.

There is also a performance angle worth raising. A function wrapped around a column is not sargable, so WHERE COALESCE(status, 'new') = 'new' cannot use an index on status even though WHERE status IS NULL OR status = 'new' can. The same expression in a WHERE clause and in a projection are not equally harmless.

COALESCE in the projection is the cleanest way to make the output readable, and it keeps the raw column visible alongside it so nobody loses the information that it was missing.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80),
  referral_code VARCHAR(40)
);

INSERT INTO customers (id, name, city, referral_code) VALUES
  (1, 'Ada Lovelace',   'London',    'REF-1'),
  (2, 'Grace Hopper',   NULL,        NULL),
  (3, 'Alan Turing',    'Cambridge', '');

SELECT id,
       name,
       city,
       COALESCE(city, 'Unknown') AS city_display,
       COALESCE(referral_code, 'None') AS referral_display
FROM customers
ORDER BY id;

Three rows come back, and every row now has something readable in the two display columns. Ada Lovelace shows London and REF-1, Grace Hopper shows Unknown and None, and Alan Turing shows Cambridge and None — even though her referral_code holds an empty string rather than a NULL, because COALESCE only substitutes for a missing value, not for an empty one.

That last point is the trap worth spelling out. If the application writes an empty string when it has no referral code, COALESCE leaves it alone and the report shows a blank cell. The fix is either to make the application write NULL consistently, or to normalise on the way in with NULLIF(referral_code, ''), which converts the empty string into a real NULL — and then COALESCE does its job.

Keep the raw column in the output alongside the display column, as the query does. That way a downstream consumer can tell the difference between a genuine value and a substituted default, and the report stays honest about which rows had data.