Semi-Joins and Anti-Joins
By the end of this lesson, you'll be able to:
- Use a semi-join to filter records based on a related object
- Use an anti-join to find records with no matching related records
Prerequisites: Parent-to-Child and Child-to-Parent Queries
Semi-joins: IN with a subquery
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won')This returns every Account that has at least one closed-won Opportunity — the subquery produces a set of Ids, and the outer query keeps only matching rows.
Anti-joins: NOT IN with a subquery
The same shape with NOT IN flips the logic entirely — it finds records with no matching related record at all:
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)A common use case: surfacing orphaned or neglected records, like Accounts with zero Opportunities.
Finding Accounts with no Opportunities
SELECT Id, Name
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)This anti-join finds every Account with no Opportunities at all — useful for surfacing accounts that need sales attention.
Exercise
Write a semi-join that returns Contacts whose AccountId matches an Account in the 'Technology' industry.
Show hint
SELECT Id FROM Contact WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = 'Technology')
Semi-Joins and Anti-Joins — Quick Check
My Notes
Log in to keep private notes on this lesson.
Questions about this lesson
No questions yet — be the first to ask.
Log in to ask a question about this lesson.
Summary
A semi-join filters using IN with a subquery to find records that DO have a matching related record; an anti-join uses NOT IN to find records that DON'T.