SQL NULL vs Zero

Zero is a genuine, known numeric value. NULL is the absence of any value at all — the two are not interchangeable, even though both might look 'empty' on a report.

What & Why

An opens value of 0 means the send was measured and produced no opens. An opens value of NULL means no open count is recorded. Those facts are different even if a report renders both as an empty-looking cell.

See How It Works

BUSINESS QUESTION

Marketing labels each email send by whether its open count is missing, zero, or positive.

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,
  opens,
  CASE
    WHEN opens IS NULL THEN 'missing'
    WHEN opens = 0 THEN 'zero'
    ELSE 'positive'
  END AS open_state
FROM marketing.email_sends
ORDER BY send_id;

RESULT — exact output from the displayed Queryflo rows

send_id	opens	open_state
201	540	positive
202	420	positive
203	610	positive
204	0	zero

The fixture contains a measured zero but no missing opens value.

Intermediate business practice