Anti-Join
An Anti-Join in SOQL is a type of semi-join query that returns records from a parent object where no matching child records exist.
Definition
An Anti-Join in SOQL is a type of semi-join query that returns records from a parent object where no matching child records exist. It uses the NOT IN operator with a subquery to filter out records that have related records in another object. For example, selecting Accounts that do not have any related Opportunities.
In plain English
“An Anti-Join is a SOQL query that finds records that don't have a related record. For example, you can ask Salesforce 'show me all Accounts that don't have any Opportunities attached', and it gives you exactly that. It's a way of finding things by what they're missing.”
Worked example
A data steward at Aspen Hardware needs to find Accounts that have never had an Opportunity - prospects who looked promising at first but never converted to a deal. She writes an Anti-Join SOQL: SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity). The query returns the 1,400 accounts with no opportunity history. She enrolls them in a re-engagement campaign rather than letting them sit dormant. The Anti-Join is the inverse of a Semi-Join - instead of filtering by what records have related records, it filters by what they're missing.
Why Anti-Join matters
In SOQL, an Anti-Join uses the NOT IN operator with a subquery to return parent records that do not have any matching child records. The pattern looks like: SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity). This returns every Account whose Id is not referenced in the set of Opportunity.AccountId values, meaning every Account without any Opportunities.
Anti-joins are particularly useful for data hygiene queries, orphan detection, and identifying records that are missing expected relationships. They run against the standard query engine and count toward SOQL governor limits like any other query. For performance on large data volumes, the subquery side should generally be on a selective, indexed field to avoid full-table scans on large child objects.
How organizations use Anti-Join
Runs a scheduled Anti-Join query to find Accounts without any Contacts as a data quality check. Any Account that comes back in the results is flagged for review because it probably indicates incomplete data entry during lead conversion.
Used an Anti-Join to identify Cases that had no related Tasks logged, which surfaced Cases that were sitting in queues without any documented activity. This kicked off a coaching initiative for agents to log their work consistently.
Built an Anti-Join query into a nightly report to find Opportunities in Closed Won stage that had no related Products. These turned out to be data entry mistakes where reps closed deals without recording what was sold, skewing pipeline analytics.
Trust & references
Straight from the source - Salesforce's reference material on Anti-Join.
- SOQL SELECT ExamplesSalesforce Developers
Test your knowledge
Q1. What does an Anti-Join query return?
Q2. Which SOQL operator is typically used in an Anti-Join?
Q3. What is a common use case for an Anti-Join?
Discussion
Loading discussion…