6. Lighthouse

Level 5 · The NULL comparison trap

From Lauren Hayes, Email Marketing Manager

“Quick sanity check before I send a survey: I want every customer whose referral source isn't 'google_ads', including the ones where we never recorded a source. Which filter gets me that?”

Your goal

Pick the WHERE clause that returns every customer whose referral_source is not 'google_ads', including customers whose referral_source is NULL.

Which WHERE clause returns all customers whose referral_source is not 'google_ads', including customers with no recorded referral_source?

Pick one answer, then check it.

The lesson

NULL means “unknown” or “not recorded”. It is not zero, not an empty string, and not equal to anything, including another NULL. That single fact causes more wrong numbers than any other part of SQL.

Three-valued logic

SQL conditions can be true, false or unknown. Any comparison involving NULL is unknown:

expression result
NULL = 'google_ads' unknown
NULL <> 'google_ads' unknown
NULL = NULL unknown
NULL IS NULL true

WHERE keeps a row only when its condition is true. Unknown rows are dropped, silently, the same as false rows.

IS NULL and IS NOT NULL

To test for a missing value, use the dedicated operators:

SELECT customer_id, email
FROM customers
WHERE referral_source IS NULL;

WHERE referral_source = NULL is valid syntax, runs without error and returns zero rows every time. Some tools warn you; most do not.

The not-equal trap

This is the version that bites analysts in real reports. Suppose some customers have no recorded referral source and you want “everyone except google_ads”:

-- drops every NULL row
WHERE referral_source <> 'google_ads'

For a NULL row, NULL <> 'google_ads' is unknown, so the row disappears. Your “not from Google Ads” audience is smaller than it should be, and the rows you lost are exactly the ones you know least about. The fix is to say what you mean about NULLs:

WHERE referral_source <> 'google_ads' OR referral_source IS NULL

Postgres, DuckDB and BigQuery also support the null-safe comparison referral_source IS DISTINCT FROM 'google_ads', which treats NULL as an ordinary value. MySQL’s version is the <=> operator: NOT (referral_source <=> 'google_ads').

NULL with AND and OR

  • true AND unknown is unknown; false AND unknown is false.
  • true OR unknown is true; false OR unknown is unknown.
  • NOT unknown is still unknown, which is why NOT referral_source = 'google_ads' also drops NULLs.

Interview note

“What does SELECT COUNT(*) FROM customers WHERE referral_source <> 'google_ads' miss?” is a classic screening question. The answer interviewers want: rows where referral_source is NULL, because the comparison is unknown, and WHERE keeps only true rows. Mention IS NULL or IS DISTINCT FROM as the fix, and you will sound like someone who has been burned by it before.