SQL JOIN Performance

Joins are usually cheap when both sides have the right index — expensive when neither does.

What & Why

A join's cost depends heavily on whether the join column is indexed on both sides. Without an index, matching rows between two tables often means scanning one side entirely for every row of the other — expensive at scale.

See How It Works

BUSINESS QUESTION

Marketing summarizes recent leads only for active campaigns.

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 recent_leads
FROM marketing.campaigns c
LEFT JOIN marketing.leads l
  ON l.campaign_id = c.id
  AND l.created_at >= TIMESTAMPTZ '2024-02-01 00:00:00+00'
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY recent_leads DESC, campaign_id;

RESULT — active campaigns with leads since February 1

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

The fixed join predicate counts the February and March leads while LEFT JOIN preserves campaigns with zero qualifying leads.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice