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.