SQL JOIN Using WHERE

An older style: list both tables in FROM separated by a comma, and state the join condition in WHERE instead of ON — worth recognizing, not writing.

What & Why

Before JOIN ... ON became standard, the same result was achieved by listing multiple tables in FROM, comma-separated, with the matching condition in WHERE instead — sometimes called an "implicit join." It still works today, but is outdated style.

See How It Works

BUSINESS QUESTION

Marketing reads an older query that links campaign names to lead ids through a WHERE condition.

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.name AS campaign_name,
  l.id AS lead_id
FROM marketing.campaigns c, marketing.leads l
WHERE l.campaign_id = c.id
ORDER BY lead_id;

RESULT — exact output from the displayed Queryflo rows

campaign_name	lead_id
Spring Launch	301
Spring Launch	302
Retention Webinar	303
Finance Retargeting	304

The legacy WHERE equality produces the same four key matches as an inner join.

Intermediate business practice