Comparison Operators

You can query values in a field where another field on the same object has a specified set of values, using IN. For example:

1SELECT Name FROM Account
2WHERE BillingState IN ('California', 'New York')

In addition, 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. 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.

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 do not 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 also 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 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 is compared. A full list of restrictions that prevent unnecessary processing is provided at the end of this section.

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 do not 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 is not Web:

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

This 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.

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  )

Because a great deal of processing work is required for semi-join and anti-join queries, Salesforce imposes the following restrictions to maintain the best possible performance:

• Basic limits:
  • No more than two IN or NOT IN statements per WHERE clause.
  • You cannot use the NOT operator as a conjunction with semi-joins and anti-joins. Using them converts a semi-join to an anti-join, and vice versa. Instead of using the NOT operator, write the query in the appropriate semi-join or anti-join form.
• Main query limits:

  The following restrictions apply to the main WHERE clause of a semi-join or anti-join query:

  • The left operand must query a single ID (primary key) or reference (foreign key) field. The selected field in a subquery can be a reference field. For example:
    1SELECT Id 
    2 FROM Idea 
    3 WHERE (Id IN (SELECT ParentId FROM Vote WHERE CreatedDate > LAST_WEEK AND Parent.Type='Idea'))

  • The left operand can't use relationships. For example, the following semi-join query is invalid due to the Account.Id relationship field:
    1SELECT Id
    2FROM Contact
    3WHERE Account.Id IN
    4  (
    5   SELECT ...
    6  )

• Subquery limits:
  • A subquery must query a field referencing the same object type as the main query.
  • There is no limit on the number of records matched in a subquery. Standard SOQL query limits apply to the main query.
  • The selected column in a subquery must be a foreign key field, and cannot traverse relationships. This means that you cannot use dot notation in a selected field of a subquery. For example, the following query is valid:
    1SELECT Id, Name
    2FROM Account
    3WHERE Id IN
    4  (
    5    SELECT AccountId
    6    FROM Contact
    7    WHERE LastName LIKE 'Brown_%'
    8  )

    Using Account.Id (dot notation) instead of AccountId is not supported. Similarly, subqueries like Contact.AccountId FROM Case are invalid.

  • You cannot query on the same object in a subquery as in the main query. You can write such self semi-join queries without using semi-joins or anti-joins. For example, the following self semi-join query is invalid:
    1SELECT Id, Name
    2FROM Account
    3WHERE Id IN
    4  (
    5    SELECT ParentId
    6    FROM Account
    7    WHERE Name = 'myaccount'
    8  )

    However, it is very simple to rewrite the query in a valid form, for example:

    1SELECT Id, Name
    2FROM Account
    3WHERE Parent.Name = 'myaccount'

  • You cannot nest a semi-join or anti-join statement in another semi-join or anti-join statement.
  • You can use semi-joins and anti-joins in the main WHERE statement, but not in a subquery WHERE statement. For example, the following query is valid:
    1SELECT Id 
    2 FROM Idea 
    3 WHERE (Idea.Title LIKE 'Vacation%') 
    4AND (Idea.LastCommentDate > YESTERDAY) 
    5AND (Id IN (SELECT ParentId FROM Vote
    6            WHERE CreatedById = '005x0000000sMgYAAU'
    7             AND Parent.Type='Idea'))

    The following query is invalid since the nested query is an additional level deep:

    1SELECT Id 
    2 FROM Idea 
    3 WHERE 
    4  ((Idea.Title LIKE 'Vacation%')
    5  AND (CreatedDate > YESTERDAY)
    6  AND (Id IN (SELECT ParentId FROM Vote
    7              WHERE CreatedById = '005x0000000sMgYAAU'
    8               AND Parent.Type='Idea')
    9  )
    10  OR (Idea.Title like 'ExcellentIdea%'))

  • You cannot use subqueries in conjunction with OR.
  • COUNT, FOR UPDATE, ORDER BY, and LIMIT are not supported in subqueries.
  • The following objects are not currently supported in subqueries:
    • ActivityHistory
    • Attachments
    • Event
    • EventAttendee
    • Note
    • OpenActivity
    • Tags (AccountTag, ContactTag, and all other tag objects)
    • Task