SQL NULLIF (NULL Handling)

The reverse of COALESCE — turns a specific value INTO NULL, most commonly used to guard against division by zero.

What & Why

NULLIF(a, b) returns NULL if a equals b, otherwise returns a unchanged. The single most common use: preventing a division-by-zero error by converting a zero denominator into NULL first — since dividing by NULL gives NULL, not an error.

See How It Works

BUSINESS QUESTION

Marketing wants click-through rate protected when a send has zero recipients.

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

EXAMPLE QUERY

SELECT
  id AS send_id,
  ROUND(100.0 * clicks / NULLIF(recipients, 0), 2) AS click_rate
FROM marketing.email_sends
ORDER BY send_id;

RESULT — exact output from the displayed Queryflo rows

send_id	click_rate
201	15.00
202	12.00
203	15.00
204	NULL

NULLIF turns Send 204's zero recipients into a safe NULL denominator.

Now You Try

Practice this concept

Marketing wants click-through rate protected when a send has zero recipients.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idclick_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