Comparison Operators

The following table lists the comparisonOperator values that are used in fieldExpression syntax. Note that comparisons on strings are case-insensitive.

Operator	Name	Description
`=`	`Equals`	Expression is true if the value in the specified `fieldName` equals the specified `value` in the expression. String comparisons using the equals operator are case-insensitive.
`!=`	`Not equals`	Expression is true if the value in the specified `fieldName` does not equal the specified `value`.
`<`	`Less than`	Expression is true if the value in the specified `fieldName` is less than the specified `value`.
`<=`	`Less or equal`	Expression is true if the value in the specified `fieldName` is less than, or equals, the specified `value`.
`>`	`Greater than`	Expression is true if the value in the specified `fieldName` is greater than the specified `value`.
`>=`	`Greater or equal`	Expression is true if the value in the specified `fieldName` is greater than or equal to the specified `value`.
`LIKE`	`Like`	Expression is true if the value in the specified `fieldName` matches the characters of the text string in the specified `value`. The `LIKE` operator in SOQL and SOSL is similar to the `LIKE` operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards. The `%` and `_` wildcards are supported for the `LIKE` operator. The `%` wildcard matches zero or more characters. The `_` wildcard matches exactly one character. The text string in the specified `value` must be enclosed in single quotes. The `LIKE` operator is supported for string fields only. The `LIKE` operator performs a case-insensitive match, unlike the case-sensitive matching in SQL. The `LIKE` operator in SOQL and SOSL supports escaping of special characters `%` or `_`. Do not use the backslash character in a search except to escape a character. For example, the following query matches Appleton, Apple, and Bappl , but not Appl: SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl_%'
`IN`	`IN`	If the value equals any one of the specified values in a `WHERE` clause. For example: SELECT Name FROM Account WHERE BillingState IN ('California', 'New York') Note that the values for `IN` must be in parentheses. String values must be surrounded by single quotes. `IN` and `NOT IN` can also be used for semi-joins and anti-joins when querying on ID (primary key) or reference (foreign key) fields.
`NOT IN`	`NOT IN`	If the value does not equal any of the specified values in a `WHERE` clause. For example: SELECT Name FROM Account WHERE BillingState NOT IN ('California', 'New York') Note that the values for `NOT IN` must be in parentheses, and string values must be surrounded by single quotes. There is also a logical operator `NOT`, which is unrelated to this comparison operator.
`INCLUDES EXCLUDES`		Applies only to multi-select picklists.

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

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

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:

SELECT Id
FROM Task 
WHERE WhoId IN 
  (
    SELECT Id
    FROM Contact
    WHERE MailingCity = '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 do not have any open opportunities:

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

Reference Field Anti-Join

The following query returns opportunity IDs for all contacts whose source is not Web:

SELECT Id
FROM Opportunity
WHERE AccountId NOT IN 
  (
    SELECT AccountId
    FROM Contact
    WHERE LeadSource = 'Web'
  )

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

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.

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 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:
```
SELECT Id 
 FROM Idea 
 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:
```
SELECT Id
FROM Contact
WHERE Account.Id IN
  (
   SELECT ...
  )
```
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'
             AND Parent.Type='Idea'))
```
  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'
               AND Parent.Type='Idea')
  )
  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

Comparison Operators

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

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