Salesforce Dictionary - Free Salesforce GlossarySalesforce Dictionary
DictionaryAAnti-Join
DevelopmentIntermediate

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.

§ 01

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.

§ 02

In plain English

👋 Study buddy

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.

§ 03

Worked example

scenario · real-world use

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.

§ 04

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.

§ 05

How organizations use Anti-Join

TerraForm Tech

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.

CodeBridge

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.

Quantum Labs

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

Official documentation

Straight from the source - Salesforce's reference material on Anti-Join.

Was this entry helpful?
Help us write better definitions. Quick reactions or detailed edit suggestions.
§

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…

Loading discussion…