Level 7 · Business accounts with an unknown or friend referral

This filter brings together two lessons from this chapter: missing values need IS NULL, and an OR mixed with AND needs parentheses.

NULL is not a value you can compare

referral_source is NULL for 666 customers: they skipped the “How did you hear about us?” question. A comparison with NULL is neither true nor false, it is unknown, and WHERE only keeps rows where the condition is true. So referral_source = NULL keeps nothing, and referral_source <> 'friend' also silently drops the NULL rows. To ask for missing values, write IS NULL; for present ones, IS NOT NULL.

Put the OR in parentheses

Ryan wants business accounts from late 2024, and within those, two kinds of referral. The “two kinds” part is an OR, and it must be grouped:

SELECT COUNT(*) AS leads
FROM customers
WHERE segment = 'business'
  AND signup_date >= '2024-07-01'
  AND signup_date < '2025-01-01'
  AND (referral_source = 'friend' OR referral_source IS NULL);

That counts 23 leads. Remove the parentheses and the count jumps to 203, because AND binds before OR and the query becomes “business late-2024 accounts with no source, plus every customer referred by a friend at any time”. The ticket would go to sales with consumers from 2023 on it. COUNT(*) is covered properly in chapter 4; here it is just a quick way to see the row count change.

Common mistakes

• Writing referral_source IN ('friend', NULL). IN compares with =, so the NULL in the list never matches. Use IN for real values and a separate IS NULL.
• Using BETWEEN '2024-07-01' AND '2024-12-31'. On this DATE column it happens to work, but the half-open range keeps working on timestamps too, so it is the safer habit.
• Forgetting the segment. Without it, consumer accounts make up most of the list.

In SQL Server

IS NULL, OR and the parentheses work exactly the same. You may see ISNULL(referral_source, '') IN ('friend', '') used as a shortcut there; it works, but it also matches real empty strings and hides the intent. The plain version reads better in every database:

WHERE segment = 'business'
  AND signup_date >= '2024-07-01' AND signup_date < '2025-01-01'
  AND (referral_source = 'friend' OR referral_source IS NULL);