lesson 25 Joins & sets · intermediate

ON versus WHERE in a LEFT JOIN

Place filters carefully when preserving unmatched rows.

the idea

ON decides which right-side rows may match. WHERE filters the joined output. Put a success condition in ON when you need to keep accounts with no successful requests.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

ai_accounts4 columns · 6 rows

idinteger · key	plantext	regiontext	signed_up_attext
1	pro	US	2025-01-01
2	free	US	2025-01-02
3	team	EU	2025-01-02
4	pro	IN	2025-01-03
5	free	EU	2025-01-04

showing 5 of 6 rows

ai_requests7 columns · 12 rows

idinteger · key	account_idinteger	modeltext	requested_attext	input_tokensinteger	output_tokensinteger	statustext
1	1	swift	2025-01-01 10:00:00	1000	200	success
2	1	reason	2025-01-02 11:00:00	2000	600	success
3	2	swift	2025-01-02 12:00:00	500	0	error
4	3	reason	2025-01-02 13:00:00	8000	2000	success
5	3	swift	2025-01-03 14:00:00	4000	1000	success

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT a.id,COUNT(r.id) AS successful_requests
FROM ai_accounts a
LEFT JOIN ai_requests r ON r.account_id=a.id AND r.status='success'
GROUP BY a.id;

Watch for: A WHERE r.status filter discards unmatched accounts and changes the result.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

The dangerous filter. Filtering success in WHERE loses the account without successful requests.

step-1.sql

SELECT a.id,COUNT(r.id) AS successful_requests
FROM ai_accounts a
LEFT JOIN ai_requests r ON r.account_id=a.id
WHERE r.status='success'
GROUP BY a.id;

Filter matching rows in ON. All accounts stay; only successful requests can match.

step-2.sql

SELECT a.id,COUNT(r.id) AS successful_requests
FROM ai_accounts a
LEFT JOIN ai_requests r ON r.account_id=a.id AND r.status='success'
GROUP BY a.id;