SOQL IN, NOT IN, Semi-Joins, and Anti-Joins with Practical Examples
IN starts as a convenient way to match a set of values, but in SOQL it also enables powerful relationship filters through semi-joins.
IN with Literal Values
SELECT Id, Name, Industry
FROM Account
WHERE Industry IN ('Technology', 'Banking', 'Healthcare')
NOT IN
SELECT Id, Name, Type
FROM Account
WHERE Type NOT IN ('Partner', 'Competitor')
In Apex, prefer bind variables when the values come from code:
Set<String> industries = new Set<String>{'Technology', 'Banking'};
List<Account> accounts = [
SELECT Id, Name
FROM Account
WHERE Industry IN :industries
];
Semi-Join: Accounts with Won Opportunities
A semi-join filters the main object using a subquery:
SELECT Id, Name
FROM Account
WHERE Id IN (
SELECT AccountId
FROM Opportunity
WHERE IsWon = true
)
The subquery is not being selected as nested output. It determines which Accounts qualify.
Anti-Join: Accounts Without Opportunities
SELECT Id, Name
FROM Account
WHERE Id NOT IN (
SELECT AccountId
FROM Opportunity
WHERE AccountId != null
)
This answers a relationship-existence question without retrieving all Accounts and Opportunities into application memory.
Semi-Join vs Parent-to-Child Subquery
Use a parent-to-child subquery when you want the child rows returned with the parent.
Use a semi-join when child criteria determine whether the parent should be returned at all.
Restrictions Matter
SOQL semi-joins and anti-joins have structural restrictions around nesting, supported subquery shapes, and combinations. Keep the query simple and consult the current Salesforce reference before generating complex query builders dynamically.