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:

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

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

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:

Reference Field Anti-Join

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

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

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:

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 can’t use the NOT operator as a conjunction with semi-joins and anti-joins. Using it converts a semi-join to an anti-join, and the reverse. 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:
  • The left operand can't use relationships. For example, the following semi-join query is invalid due to the Account.Id relationship field:
• Subquery limits:
  • A subquery must query a field referencing the same object type as the main query.
  • There’s 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 can’t traverse relationships. This limit means that you can’t use dot notation in a selected field of a subquery. For example, the following query is valid:

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

  • You can’t 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:
    However, it’s simple to rewrite the query in a valid form, for example:
  • You can’t 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:

    The following query is invalid since the nested query is an extra level deep:
  • You can’t use subqueries with OR.
  • COUNT, FOR UPDATE, ORDER BY, and LIMIT aren’t supported in subqueries.
  • The following objects aren’t currently supported in subqueries:
    • ActivityHistory
    • Attachments
    • Event
    • EventAttendee
    • Note
    • OpenActivity
    • Tags (AccountTag, ContactTag, and all other tag objects)
    • Task