SQL: Why an INNER JOIN Drops Rows with NULL Keys

The symptom

You join sessions to campaigns to put a campaign name on each session, and the report shrinks. Website Analytics has 30 sessions; after the join there are 16. Whole channels vanish: the organic and direct rows aren't low, they aren't there. Total time on site drops from 9,162 seconds to 4,652. There's no error, and every number that remains is correct.

Why it happens

14 sessions have no campaign_id: the visitor arrived by search or typed the address, so the column is NULL. An INNER JOIN keeps a pair of rows only when its ON condition is true, and c.campaign_id = NULL is never true. It's unknown, for every campaign, the same way NULL = NULL is unknown. So the 14 sessions match nothing and leave the result without a trace.

This is the rule a WHERE clause follows, applied to a join: a row survives only on a definite yes. Lesson 2.8 · True, false, and unknown walks through it. The join just makes it harder to see, because the rows disappear before any filter you wrote.

The fix

If rows without a partner belong in the report, use a LEFT JOIN. Every session survives, and the ones with no campaign get NULL in the campaign columns. Give that NULL a readable label with coalesce so the report says what it means:

select s.channel, c.campaign_name, count(*) as sessions
from sessions as s
join campaigns as c on c.campaign_id = s.campaign_id
group by s.channel, c.campaign_name
order by s.channel, c.campaign_name

select s.channel,
       coalesce(c.campaign_name, '(no campaign)') as campaign_name,
       count(*) as sessions
from sessions as s
left join campaigns as c on c.campaign_id = s.campaign_id
group by s.channel, c.campaign_name
order by s.channel, campaign_name

The fixed query returns all 30 sessions in six rows, and the two rows the inner join hid are the biggest story in the data: 10 organic sessions and 4 direct ones, nearly half of all traffic, with no campaign at all.

Two NULL keys don't match each other either

Joining two tables that both have NULL keys doesn't pair them up. When the two NULLs should match, as when you group by a nullable column and join the totals back, write the condition as a.key is not distinct from b.key. The NULL join keys section of the JOIN types reference runs that case.

NULL handling is part of every query

A missing value doesn't announce itself. It turns a comparison into unknown, and every clause that keeps rows on a definite yes, whether WHERE, ON, or HAVING, quietly drops it. The same few rules produce most of the wrong answers involving NULL:

• Why = NULL never matches: test with IS NULL.
• Why NOT IN with a NULL returns nothing: one NULL in the list empties the result.
• Why a LEFT JOIN turns into an INNER JOIN: a WHERE filter rejects the NULLs the join added.
• Why COUNT(column) skips NULL values: aggregates skip NULL.

Before trusting a result, ask which columns it touches can be NULL, and what each clause does with them.

Learn more

• Lesson 5.3 · INNER vs LEFT JOIN
• Lesson 5.5 · FULL OUTER JOIN
• Reference · NULL handling
• Reference · JOIN types