SQL NULL in JOINs

A LEFT JOIN fills in NULL for every column on the unmatched side — the same mechanism covered in the Joins series, revisited here as a NULL-specific concept.

What & Why

When a LEFT JOIN finds no match for a row, every column that would have come from the right table becomes NULL — not just one column, all of them. This is exactly the same behavior the Joins series covered, worth reinforcing here as a direct application of what NULL actually means: "no matching data exists."

See How It Works

BUSINESS QUESTION

Marketing wants every campaign plus its lead count, including campaigns with no leads.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

EXAMPLE QUERY

SELECT
  c.id AS campaign_id,
  c.name,
  COUNT(l.id) AS lead_count
FROM marketing.campaigns c
LEFT JOIN marketing.leads l ON l.campaign_id = c.id
GROUP BY c.id, c.name
ORDER BY campaign_id;

RESULT — exact output from the displayed Queryflo rows

campaign_id	name	lead_count
1	Spring Launch	2
2	Retention Webinar	1
3	Finance Retargeting	1
4	Enterprise Search	0

The unmatched campaign remains and COUNT(l.id) correctly returns zero.

Intermediate business practice