SQL Semi-Join Pattern

The opposite of anti-join — rows from A that DO have a match in B, without duplicating them.

What & Why

A semi-join returns rows from table A that have at least one match in table B — but each A row appears only once, even if it matches multiple B rows. EXISTS and IN naturally produce this; a regular JOIN does not (it duplicates A rows per match).

See How It Works

BUSINESS QUESTION

List product features that have received feedback.

id	name	description	team	released_at	deprecated_at
1	Activation Checklist	Guides new users through setup	growth	2024-01-20	NULL
2	CSV Export	Exports report data	platform	2024-02-14	NULL
3	Invite Nudges	Prompts workspace collaboration	growth	2024-03-05	NULL
4	Legacy Dashboard	Original reporting surface	analytics	2023-08-10	2024-06-01

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

SELECT
  f.id,
  f.name,
  f.team
FROM product.features f
WHERE EXISTS (
  SELECT 1
  FROM product.feedback fb
  WHERE fb.feature_id = f.id
)
ORDER BY f.name;

RESULT — features with feedback

id	name	team
1	Activation Checklist	growth
2	CSV Export	platform
3	Invite Nudges	growth

EXISTS preserves feature grain even when a feature has multiple feedback rows.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice