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

IN Operator/Clause in SOQL(Salesforce Object Query Language)
by: Bijay Kumar
blow post content copied from  SalesForce FAQs
click here to view original post



### Summary of the Content This content provides a tutorial on using the **IN operator/clause** in **SOQL (Salesforce Object Query Language)** to filter Salesforce records based on multiple specified values. The IN clause allows users to retrieve records where a field's value matches any value from a given list, making it more efficient than using multiple OR conditions. #### Key Points: 1. **Functionality of the IN Clause**: - It filters records by matching fields against multiple values. - It can take a collection (List or Set), allowing for dynamic queries. 2. **Basic Syntax**: - `SELECT fields FROM Object_Name WHERE Field_Name IN (Value1, Value2, Value3)` 3. **Examples**: - **Basic Example**: Retrieve accounts in specific industries (e.g., Banking, Technology, Healthcare). - **Using IDs**: Fetch opportunity records related to specific account IDs. - **Parent-Child Relationships**: Use subqueries to filter parent accounts based on child records (e.g., contacts). - **Child-Parent Relationships**: Retrieve contacts related to accounts in a specific industry. - **Using Collections in Apex**: Demonstrates how to use the IN clause with dynamic values in Apex code. 4. **Conclusion**: The IN operator is a powerful tool in SOQL that simplifies queries and enhances performance by reducing the need for multiple OR conditions. ### Additional Context The IN clause is particularly useful in Salesforce environments where data relationships are complex, and efficient querying is essential for performance. Understanding how to leverage this operator can significantly enhance a developer's ability to work with Salesforce data effectively. ### Hashtags for SEO #Salesforce #SOQL #INOperator #SalesforceTutorial #DataFiltering #SalesforceDevelopment #Apex #SalesforceQueries #CRM #SalesforceBestPractices


While working on Salesforce records, I need to filter records by matching fields against multiple specified values. To retrieve those records, we have an IN operator/clause in SOQL using we can 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.

Explain the IN Operator/Clause in SOQL

When we have a number of records in Salesforce so, 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 to query 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 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 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 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.’

IN Operator/Clause in SOQL

Example: Use IN Clause With IDs in SOQL

Now, we will understand how we can use the IN operator/clause to retrieve records when we pass 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 account IDs provided in the IN clause.

In Operator in SOQL

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, here, we will use the subquery to get all parent accounts related to contact, and to get those records, we will use 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 fetches accounts whose ID exists in the list returned by the subquery. That means we fetched only those accounts that have contact(child) records.

Use IN Clause in SOQL

Example: Query Child Records Using IN Clause in SOQL

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

In the below query, the subquery retrieves Ids of Accounts where 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 industry value as technology.

IN Operator Clause in SOQL

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 IN Clause With Collection in Apex

Now, let’s understand how we can use the IN operator/clause with collection 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 understand the example: we want to pass dynamic value to the IN clause in the SOQL query in Salesforce Apex to retrieve the records from the account object, which has the subscription type Diamond and Gold.

In the below Apex code, we created a class with a method and list collection variable. In that list collection, we stored values from the Subscription Type custom field. After that, again, we created a list collection in which we stored records that we fetched using SOQL query; here, we used the IN clause in SOQL query and passed the list collection variable where we 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 below query result 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 SOQL query and avoid using multiple OR conditions in 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.

You may like to read:

The post IN Operator/Clause in SOQL(Salesforce Object Query Language) appeared first on SalesForce FAQs.


January 30, 2025 at 07:21PM
Click here for more details...

=============================
The original post is available in SalesForce FAQs by Bijay Kumar
this post has been published as it is through automation. Automation script brings all the top bloggers post under a single umbrella.
The purpose of this blog, Follow the top Salesforce bloggers and collect all blogs in a single place through automation.
============================

Salesforce