Anti-Join
An Anti-Join in SOQL is a query pattern that returns records from one object only when they do not have a matching record on a related object.
Definition
An Anti-Join in SOQL is a query pattern that returns records from one object only when they do not have a matching record on a related object. The canonical example is finding Accounts with no Opportunities: SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity). The outer query asks for Accounts; the inner subquery returns every Account that already has an Opportunity. The NOT IN clause subtracts the inner set from the outer. The result is Accounts that have never had an Opportunity attached. The opposite pattern, a Semi-Join, returns records that do have a match by using IN instead of NOT IN.
Anti-joins are how Salesforce admins and developers answer negative-existence questions through SOQL alone, without writing Apex. Common business questions like which Accounts have no recent activity, which Leads have not been emailed, or which Cases lack a Knowledge attachment all translate cleanly into anti-join queries. Salesforce's query optimizer treats the pattern specially: there are stricter selectivity requirements on the inner query, and anti-joins on non-selective parents can fail with non-selective filter errors that selectively-matched queries would not.
How Anti-Joins are written and where the SOQL optimizer pushes back
The NOT IN pattern
The standard form is: SELECT fields FROM ParentObject WHERE Id NOT IN (SELECT ParentId FROM ChildObject WHERE optional filters). The outer query returns parents; the inner query returns parent IDs that have at least one child match. NOT IN subtracts. The result is parents with no qualifying children. The pattern works for any parent-child relationship, and the inner query can include filters to narrow which children disqualify a parent.
Anti-joins in child-to-parent direction
The pattern also works in reverse. SELECT Id FROM Contact WHERE AccountId NOT IN (SELECT Id FROM Account WHERE Industry = 'Technology') returns Contacts whose Account is not in Technology. The outer query is the Contact, and the inner narrows the parent set. Anti-join syntax is identical; only the field reference changes.
Semi-Join, the positive counterpart
Replace NOT IN with IN and the same query becomes a Semi-Join. Semi-joins return records that have a match. The two patterns share syntax but answer opposite questions. SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won') returns Accounts with at least one closed-won deal. SELECT Id FROM Account WHERE Id NOT IN the same subquery returns Accounts that have never closed a deal.
Optimizer behaviour and selectivity
Salesforce's SOQL optimizer uses selective filters to choose indexes. Anti-joins are harder to optimise than semi-joins because the optimizer must scan the inner result and the outer set, then subtract. The platform enforces stricter rules: the inner query must filter on indexed fields, both queries must respect 200,000-record limits, and the outer query usually requires its own selective filter when running over a million-record object. Hitting a non-selective filter error in production is a strong signal that the anti-join needs a tighter outer or inner filter.
Limits and platform-imposed caps
Anti-joins cap the inner query at 200,000 rows. Above that, the query errors out. Anti-joins on the same object as the outer query (self-referential) are not supported. Only one inner semi-join or anti-join is allowed per query (or three when filtering nested objects); writing two NOT IN subqueries in the same WHERE clause errors at parse time. These limits force complex anti-joins to be split into multiple queries or implemented in Apex.
Common business questions answered with anti-joins
Anti-joins answer questions like which Accounts have no Opportunities, which Leads have no Tasks logged this quarter, which Contacts are not on a Campaign, which Cases have no Knowledge attachment, and which Opportunities have no Products attached. The pattern crops up several times a week in any operational report request, and writing it in SOQL is dramatically faster than walking record relationships in Apex.
Reports versus SOQL anti-joins
Standard Salesforce reports support a similar pattern through Cross Filters. The Accounts report type plus a Without Opportunities cross filter is the no-code equivalent of an anti-join SOQL query. Cross filters are easier for admins to build, but they cap at five per report and at simpler join logic than custom SOQL. For anything beyond the straightforward without case, SOQL is the right tool.
Performance tuning anti-joins
Three tactics consistently help. Filter the inner query aggressively. The fewer disqualifying rows it returns, the faster the outer subtraction is. Add a selective filter to the outer query so the optimizer can use an index. Run the query against a representative dataset before deploying; what passes in a small sandbox can fail with non-selective errors against production volumes. The Query Plan Tool in the Developer Console is the right place to inspect the planned execution path.
How to write a SOQL Anti-Join
The pattern is short to write. Most of the work is choosing the filters that keep the inner result under 200,000 rows and the outer query selective enough to avoid non-selective filter errors.
- Identify the business question in negative terms
Rewrite the question to start with which records have no. Which Accounts have no recent Opportunities, which Leads have no logged Tasks, and so on. The negative framing maps directly to NOT IN.
- Write the inner query against the child object
Draft SELECT ParentId FROM ChildObject WHERE filters. Add filters that narrow the disqualifying set as much as possible. Make sure the filter uses indexed fields.
- Wrap with NOT IN on the parent
SELECT fields FROM ParentObject WHERE Id NOT IN (the inner query) AND optional outer filters. Add an outer filter (CreatedDate, Industry, etc.) to make the outer query selective too.
- Run the Query Plan Tool
Developer Console, Query Editor, then Explain Plan on the SOQL. Confirm that the optimizer can use an index and that the cost is acceptable. Non-selective plans usually need tighter filters.
- Validate against representative data
Run the query against a full-volume sandbox before promoting to production. Anti-joins that pass in small data can fail at scale; volume testing catches that early.
- The inner query caps at 200,000 rows. Beyond that, the query errors out. Tighten the inner filter or split into multiple queries.
- Only one semi-join or anti-join is allowed per query (up to three when nested with related objects). Two NOT IN subqueries in the same WHERE clause fail at parse time.
- Self-referential anti-joins (outer and inner on the same object) are not supported. Use Apex or a different pattern for self-referencing logic.
- Non-selective outer queries trigger non-selective filter errors in production-scale orgs. Always run Explain Plan before relying on the query.
Trust & references
Cross-checked against the following references.
- SOQL IN and NOT IN OperatorsSalesforce Developer Docs
- Cross Filters in ReportsSalesforce Help
Straight from the source - Salesforce's reference material on Anti-Join.
- Semi-Joins and Anti-JoinsSalesforce Developer Docs
About the Author
Dipojjal Chakrabarti is a B2C Solution Architect with 29 Salesforce certifications and over 13 years in the Salesforce ecosystem. He runs salesforcedictionary.com to help admins, developers, architects, and cert/interview candidates sharpen their fundamentals. More about Dipojjal.
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…