SQL NULLIF

The mirror image of COALESCE — turns a specific value INTO NULL, most often to guard against a division by zero.

What & Why

NULLIF(value1, value2) compares its two arguments: if they're equal, it returns NULL; otherwise, it returns value1 unchanged. Where COALESCE replaces a NULL with something else, NULLIF does the opposite — it deliberately turns a specific real value into a NULL.

The single most common use is guarding a division against a zero denominator. Dividing by zero raises an error in Postgres — but dividing by NULL just produces NULL as the result, which is usually a far more graceful way to handle it in a report.

See How It Works

BUSINESS QUESTION

Marketing wants click rates for every email send without a zero-recipient send crashing the report.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

Watch NULLIF neutralize a matching valueExample 1 of 4

clicks::numeric / NULLIF(recipients, 0)

id	clicks	recipients	click_rate
201	180	1200	0.1500
202	96	800	?
203	225	1500	?
204	0	0	?

Recipients is non-zero, so NULLIF returns 1200 and division succeeds.

EXAMPLE QUERY

SELECT
  id AS send_id,
  clicks,
  recipients,
  clicks::numeric / NULLIF(recipients, 0) AS click_rate
FROM marketing.email_sends
ORDER BY send_id
LIMIT 20;

Now You Try

Practice this concept

Marketing wants click rates for every email send without a zero-recipient send crashing the report.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idclicksrecipientsclick_rate

marketing.email_sends

Column	Type
id	integer
campaign_id	integer
sent_at	timestamp with time zone
recipients	integer
opens	integer
clicks	integer
unsubscribes	integer
bounces	integer
legacy_send_group	text

query.sql

Intermediate business practice