Comparison Operators

A semi-join is a subquery on another object in an IN clause to restrict the records returned. An anti-join is a subquery on another object in a NOT IN clause to restrict the records returned. You can create more complex queries by replacing the list of values in the IN or NOT IN clause with a subquery. The subquery can filter by ID (primary key) or reference (foreign key) fields.

Sample uses of semi-joins and anti-joins include:

• Get all contacts for accounts that have an opportunity with a particular record type.
• Get all open opportunities for accounts that have active contracts.
• Get all open cases for contacts that are the decision maker on an opportunity.
• Get all accounts that don’t have any open opportunities.

If you filter by an ID field, you can create parent-to-child semi- or anti-joins, such as Account to Contact. If you filter by a reference field, you can create child-to-child semi- or anti-joins, such as Contact to Opportunity, or child-to-parent semi- or anti-joins, such as Opportunity to Account.

ID field Semi-Join

You can include a semi-join in a WHERE clause. For example, the following query returns account IDs if an associated opportunity is lost:

1SELECT Id, Name 
2FROM Account 
3WHERE Id IN 
4  ( SELECT AccountId
5    FROM Opportunity
6    WHERE StageName = 'Closed Lost' 
7  )

This example is a parent-to-child semi-join from Account to Opportunity. Notice that the left operand, Id, of the IN clause is an ID field. The subquery returns a single field of the same type as the field to which it’s compared. For restrictions that prevent unnecessary processing of semi-join queries, see Considerations.

Reference Field Semi-Join

The following query returns task IDs for all contacts in Twin Falls:

1SELECT Id
2FROM Task 
3WHERE WhoId IN 
4  (
5    SELECT Id
6    FROM Contact
7    WHERE MailingCity = 'Twin Falls'
8  )

Notice that the left operand, WhoId, of the IN clause is a reference field. An interesting aspect of this query is that WhoId is a polymorphic reference field as it can point to a contact or a lead. The subquery restricts the results to contacts.

ID field Anti-Join

The following query returns account IDs for all accounts that don’t have any open opportunities:

1SELECT Id 
2FROM Account 
3WHERE Id NOT IN
4  (
5    SELECT AccountId
6    FROM Opportunity
7    WHERE IsClosed = false
8  )

Reference Field Anti-Join

The following query returns opportunity IDs for all contacts whose source isn’t Web:

1SELECT Id
2FROM Opportunity
3WHERE AccountId NOT IN 
4  (
5    SELECT AccountId
6    FROM Contact
7    WHERE LeadSource = 'Web'
8  )

This example is a child-to-child anti-join from Opportunity to Contact.

Multiple Semi-Joins or Anti-Joins

You can combine semi-join or anti-join clauses in a query. For example, the following query returns account IDs that have open opportunities if the last name of the contact associated with the account is like the last name “Apple”:

1SELECT Id, Name 
2FROM Account
3WHERE Id IN
4  (
5    SELECT AccountId
6    FROM Contact
7    WHERE LastName LIKE 'apple%'
8  )
9  AND Id IN
10  (
11    SELECT AccountId
12    FROM Opportunity
13    WHERE isClosed = false
14  )

You can use at most two subqueries in a single semi-join or anti-join query. Multiple semi-joins and anti-join queries are also subject to existing limits on subqueries per query.

Note

Semi-Joins or Anti-Joins Evaluating Relationship Queries

You can create a semi-join or anti-join that evaluates a relationship query in a SELECT clause. For example, the following query returns opportunity IDs and their related line items if the opportunity's line item total value is more than $10,000:

1SELECT Id, (SELECT Id from OpportunityLineItems) 
2FROM Opportunity 
3WHERE Id IN
4  (
5    SELECT OpportunityId
6    FROM OpportunityLineItem
7    WHERE totalPrice > 10000
8  )