IN Operator/Clause in SOQL(Salesforce Object Query Language)

While working on Salesforce records, I need to filter records by matching fields against multiple specified values. To retrieve those records, we use an IN operator/clause in SOQL to fetch the matching field records in Salesforce.

In this Salesforce tutorial, we will learn about the IN operator/clause in SOQL(Salesforce Object Query Language). In that, I will explain the functionality of the IN clause and how to use it in SOQL queries with different examples.

Table of Contents

Explain the IN Operator/Clause in SOQL

When we have a number of records in Salesforce, using the IN clause in SOQL, we can filter the records by matching fields against multiple specified values.

It works similarly to the IN operator in SQL and allows querying for records where a field’s value matches any value from a given list.

The IN clause allows us to query multiple values at once instead of using multiple OR conditions in a SOQL query. Then, we can also pass a collection(List or Set) into the IN clause, making queries dynamic.

Syntax: Declaring IN Operator/Clause in SOQL

SELECT fields FROM Object_Name WHERE Field_Name IN (Value1, Value2, Value3)

SELECT: The SELECT clause specifies the fields to retrieve in the query results.
FROM: The FROM clause defines the Sobject from which to retrieve data.
WHERE: The WHERE clause filters the query results based on specific conditions, as you specify.
IN: Using the IN operator/clause, we can find all records that contain provided values in a particular field.

Example:

Let’s understand the IN operator using a basic example. We want to retrieve records so that in the query result, we will get only those records in which industry is ‘Banking,’ ‘Technology,’ or ‘Healthcare.’

SELECT Id, Name, Industry  FROM Account WHERE Industry IN ('Banking', 'Technology', 'Healthcare')

In the above SOQL query, we provided fields that we want to display in the result and the object from which we want to retrieve records.

After that, in the WHERE clause, we provided a condition that we only want those records whose industry field contains the provided values with the IN clause.

Using the IN operator/clause, we don’t need to add different conditions for each value or add the OR operator. Here, we only need to provide the values for which we want to retrieve records.

In the image below, you can see the records where only those records get retrieved whose industry is ‘Banking,’ ‘Technology,’ or ‘Healthcare.’

Example: Use an IN Clause With IDs in SOQL

Now, we will understand how to use the IN operator/clause to retrieve records when passing multiple IDs.

For example, we want to retrieve opportunity records that are related to the account ID that we will provide to the IN clause. So that we only get those records in which the account ID is XYZ (provide as per your requirement).

SELECT Id, Name, Account.Name FROM Opportunity WHERE AccountId IN ('0015i00000KafC9AAJ', '0015i00000KafCCAAZ')

In the above SOQL query, we added the opportunity name and account name, and we want to retrieve records from the opportunity object. But we only want to display those records from opportunities that have provided account IDs.

In the image below, you can see we provided two account IDs, and in the query result, it displayed the opportunities that contain the account IDs provided in the IN clause.

Example: Query Parent Records Using IN Clause in SOQL

We can also use the IN operator/clause with subqueries in SOQL. Let us understand how we can use it to filter records dynamically and query records from parent-to-child relationships.

In SOQL, the IN clause filters records based on a set of values. When combined with a subquery, it allows us to filter records dynamically based on related data.

For example, we want to retrieve all accounts that are associated with contacts. Now, we will use a subquery to retrieve all parent accounts related to the contact. To do this, we will utilize the IN clause in SOQL.

SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact)

In the above SOQL query, the subquery that we added retrieves all AccountId values from contacts. The main query retrieves accounts whose ID is present in the list returned by the subquery. That means we fetched only those accounts that have contact(child) records.

Example: Query Child Records Using IN Clause in SOQL

For example, we want to find all contacts related to accounts in the “Technology” industry. Therefore, we need to query the child-to-parent relationship.

In the query below, the subquery retrieves the IDs of Accounts where the Industry is “Technology.” The main query fetches contacts related to those accounts(parent) records.

SELECT Id, Name, Account.Name FROM Contact WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = 'Technology')

In the query result, we retrieved only those contact records whose parent account contains the industry value of technology.

In this way, we can use the SOQL IN clause in parent-to-child or child-to-parent relationships to retrieve the records.

Example: Use an IN Clause With a Collection in Apex

Now, let’s understand how we can use the IN operator/clause with collections in Apex. When working with Salesforce Apex, we sometimes need to retrieve records based on a set of dynamic values. The IN clause in SOQL is useful when filtering records by a collection.

Syntax:

The IN clause allows us to filter query results by matching a field against a set of values stored in a collection (List, Set).

SELECT Id, Name FROM Account WHERE Id IN :accountIds

accountIds: is a collection of IDs (List or Set).
The (:) before accountIds is a bind variable, which binds the Apex variable to the SOQL query.

Now, let’s examine the example: we want to pass a dynamic value to the IN clause in the SOQL query in Salesforce Apex to retrieve records from the Account object, which has the subscription types Diamond and Gold.

In the code below, we created a class with a method and a list collection variable. In that list collection, we stored values from the Subscription Type custom field.

After that, we created another list collection to store records fetched using a SOQL query. Here, we used the IN clause in the SOQL query and passed the list collection variable, which stored values from the subscription type field.

Then, using the for loop, we displayed retrieved records in the debug log. In the loop, we created an account instance to fetch the fields from the object. After that, save the code and execute it using an anonymous window.

public class SOQLclause {
    
    public void ExecuteQuery(){
          List<String> types = new List<String>{'Diamond','Gold'};
          List<Account> accList = [ SELECT Id, Name, Subscription_Type__c FROM Account  
                                                                  WHERE Subscription_Type__c IN :types ];
         for (Account acc: accList){
               system.debug ( 'Name : ' + acc.Name + '    ' +'Subscription Type : ' + 
                                                acc.Subscription_Type__c );
                }
       }
}

In the query result below, you can see we only have records from the account object, which has the subscription type Diamond and Gold.

In this way, we can use the IN clause with Apex in Salesforce to retrieve the records in Apex code using a SOQL query and avoid using multiple OR conditions in the SOQL query.

Conclusion

I hope you have an idea about the IN operator/clause in SOQL(Salesforce Object Query Language). I have explained the syntax and different SOQL queries that are used in the IN clause with examples and explanations.