SQL Subquery Performance

A correlated subquery genuinely re-runs per outer row — the pattern from the Subqueries series has a real cost.

What & Why

A correlated subquery (one that references the outer row) really does execute once per outer row — at scale, that's a real performance cost, not just a stylistic concern. A non-correlated subquery runs once, regardless of outer row count.

See How It Works

BUSINESS QUESTION

Marketing returns campaigns with at least one converted lead without joining every match.

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
FROM marketing.campaigns c
WHERE EXISTS (
  SELECT 1
  FROM marketing.leads l
  WHERE l.campaign_id = c.id
    AND l.converted_at IS NOT NULL
)
ORDER BY campaign_id;

RESULT — campaigns with converted leads

campaign_id	name
1	Spring Launch
3	Finance Retargeting

EXISTS stops after it proves each qualifying campaign has a converted lead.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice