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.

Now You Try

Practice this concept

Marketing wants every campaign and its lead count, including campaigns that have no matching leads.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idnamelead_count

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Intermediate business practice