SELECT

Semi-Joins with IN and Anti-Joins with NOT IN

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

SELECT Name FROM ACCOUNT 
WHERE BillingState IN ('California', 'New York')

In addition, you can create more complex semi-joins and anti-joins using IN and NOT IN to query ID fields.

Basic Semi-Join: Use in a WHERE Clause

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

 SELECT Id, Name 
 FROM Account 
 WHERE Id IN 
(SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')

Notice that 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.

Basic Anti-Join: Use in a WHERE Clause

The following example returns account IDs for all accounts that do not have any open opportunities:

 SELECT Id 
 FROM Account 
 WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)

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":

 SELECT Id, Name 
  FROM Account
  WHERE Id IN (
    SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%'
  )
  AND Id IN (
    SELECT AccountId FROM Opportunity WHERE isClosed = false)

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. For more information, see the subquery limits described in Understanding Relationship Query Limitations.

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:

  SELECT Id, (SELECT Id from OpportunityLineItems) 
  FROM Opportunity 
  WHERE Id IN (
    SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000
  )

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

• Basic limit:
  • 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 limit: In the main WHERE clause, the left operand of any semi-join or anti-join query must query a single primary ID field. However, the selected field in a subquery can be a foreign key. For example:

  SELECT Id 
   FROM Idea 
   WHERE (Id IN (SELECT ParentId FROM Vote WHERE CreatedDate > LAST_WEEK))

• 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:

    SELECT Id, Name
    FROM Account
    WHERE Id in (
        SELECT AccountId from Contact WHERE LastName LIKE 'Brown_%'
      )

    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:

    SELECT Id, Name
    FROM Account
    WHERE Id In (
       SELECT ParentId
       FROM Account
       WHERE Name = 'myaccount'
    )

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

    SELECT Id, Name
    FROM Account
    WHERE 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:

    SELECT Id 
     FROM Idea 
     WHERE (Idea.Title LIKE 'Vacation%') 
    AND (Idea.LastCommentDate > YESTERDAY) 
    AND (Id IN (SELECT ParentId FROM Vote WHERE CreatedById = '005x0000000sMgYAAU'))

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

    SELECT Id 
     FROM Idea 
     WHERE 
      ((Idea.Title LIKE 'Vacation%')
      AND (CreatedDate > YESTERDAY)
      AND (Id IN (SELECT ParentId FROM Vote WHERE CreatedById = '005x0000000sMgYAAU')
      )
      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