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.

Now You Try

Practice this concept

Marketing wants each email send labeled missing, zero, or positive from its opens value.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idopensopen_state

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