Semi-Join and Anti-Join Filters

A semi-join is a subquery on another object in an in clause that restricts the records returned by the primary query. Conversely, an anti-join is a subquery on another object in a nin clause that restricts the records returned.

The subquery can filter by ID or a reference field.

• When you filter by an ID field, you can create parent-to-child semi-joins and anti-join, such as Account to Contact or Account to Opportunity.
• When you filter by a reference field, you can create child-to-child semi- or anti-joins, such as Contact to Opportunity. You can also create child-to-parent semi- or anti-joins, such as Opportunity to Account.

Semi-join filters help you narrow your query, such as returning only accounts whose opportunities match a specific criteria.

You can include a semi-join in a where argument. With the inq filter type for the ID field, you can restrict the records returned by the primary query.

This example returns accounts if an associated opportunity is closed won.

This example is a parent-to-child semi-join from Account to Opportunity, which uses Id on the Account and AccountId on the opportunity.

The previous query is similar to this SOQL statement.

You can also filter the subquery with multiple where conditions like this.

The previous query is similar to this SOQL statement.

To restrict the records to display only those records with associated child records, filter the Id on the subquery with ne: null.

The previous query is similar to this SOQL statement.

To filter records based on a record type, pass the RecordTypeId field to the inq filter.

This example returns cases that match a specified DeveloperName value on the RecordType field.

A reference field contains an Id value that points to a record on another object. The name of a reference field ends with Id, such as AccountId or CaseId. For example, the AccountId field on a contact identifies a unique account record.

Some objects have WhoId or WhatId reference fields, which can point to one of several other objects. The WhoId field can point to a contact or lead. See Reference Field Type.

This example returns contact requests for all contacts whose first name starts with "R".

The WhoId field in the semi-join is a polymorphic reference field because it can point to a contact or a lead. In this case, we restrict the subquery to contact records.

Anti-join filters help you narrow your query, such as returning only accounts that don’t match a certain criteria.

You can include an anti-join in a where argument. With the ninq filter type for the ID field, you can restrict the records returned by the primary query.

This example returns accounts that don’t have any open opportunities.

The previous query is similar to this SOQL statement.

You can also query accounts with associated opportunities that aren’t in prospecting stage.

The previous query is similar to the following SOQL statement.

This example returns opportunities for all contacts whose source isn’t Web. It shows a child-to-child anti-join from Opportunity to Contact.

You can combine semi-join or anti-join clauses in a query. This example returns accounts that have open opportunities if the last name of the associated account starts with "G".

The previous query is similar to this SOQL statement.

You can create a semi-join or anti-join that evaluates a relationship query. This type of query returns object of one type based on criteria that applies to objects of another type.

This example returns accounts and their associated contacts if the contact lead source is Web.

The previous query is similar to this SOQL statement.

SOQL and SOSL Reference: Semi-Joins with IN and Anti-Joins with NOT IN