In Salesforce, picklist and multi-select picklist fields offer predefined options to simplify user input when selecting values. Querying these fields requires some special considerations when working with Salesforce Object Query Language (SOQL).
In this Salesforce tutorial, I will explain how to query picklist and multi-select picklist fields in SOQL effectively.
Querying Picklist in Salesforce SOQL
In Salesforce, we can use standard SOQL comparison operators, such as ‘=,‘ ‘!=‘, ‘IN,‘ and ‘NOT IN, ‘ to query the picklist fields. In the example below, I will explain how to use these comparison operators to query picklists in Salesforce.
Let’s take an example where we will retrieve the Accounts with an Industry value of ‘Technology,’ and we will write the SOQL query in the following way.
SELECT Id, Name, Industry FROM Account WHERE Industry = 'Technology'
Using the “!=” operator: Now we will use the “!=” operator to filter records where the picklist field does not match a specific value. In this example, we will fetch the Account records where Industry values are not equal to “Healthcare.”
SELECT Id, Name, Industry FROM Account WHERE Industry != 'Healthcare'
After executing the above query, we can see in the query results that it retrieves all accounts where the Industry is not “Healthcare.“
Using the IN operator: This operator filters records where the picklist field matches any value in a specified list.
SELECT Id, Name, Industry FROM Account WHERE Industry IN ('Technology', 'Banking', 'Retail')
In the query results, we can see that the query retrieves all accounts where the Industry is either “Technology,” “Energy,” or “Transportation.”
Using NOT IN operator: This operator is used in SOQL queries to filter records where the picklist field does not match any value in a specified list.
SELECT Id, Name, Industry FROM Account WHERE Industry NOT IN ('Banking', 'Retail')
In the query results, we obtained account records with an Industry value other than “Banking” and “Retail“.
Querying Multi-Select Picklists in Salesforce SOQL
In Salesforce SOQL, we use operators like INCLUDES and EXCLUDES to filter the multiselect picklist field. By using these operators in SOQL queries, users can select more than one value from the provided list of values.
In the examples below, I will explain how to execute an SOQL query using the INCLUDES and EXCLUDES operators.
Get the Multi-select Picklist Values using EXCLUDES:
The EXCLUDES operator is used to find records that do not include one or more specific values.
In the below SOQL query, we will fetch the Opportunity records that have State value other than California.
SELECT Id, Name, States__c FROM Opportunity WHERE States__c EXCLUDES ('California')
After executing the above SOQL queries, we can see the output in the query results, which indicates that we retrieved an account with a multi-select picklist value of States_C other than California.
In the SOQL queries, we have used the WHERE States__c EXCLUDES (‘California’) clause, which excluded opportunity records with States_C values of ‘California’ and fetched other opportunity records.
Get the Multi-select Picklist Values using INCLUDES:
The INCLUDES operator is used to find records that include one or more specific values.
SELECT Id, Name, States__c FROM Opportunity WHERE States__c INCLUDES ('Florida','Colorado')
In the above SOQL query, we have used “WHERE States__c INCLUDES (‘Florida’,’Colorado’)“. Here, the INCLUDES operator includes only those opportunity records with States_C values of ‘Florida’ and ‘Colorado‘.
This way, we can get the multi-select picklist values in SOQL queries using the INCLUDES and EXCLUDES operators.
Conclusion
In this Salesforce SOQL tutorial, we have learned about the following methods for retrieving the values of picklist and multi-select fields. For retriving the picklist values we have used the operators such as ‘=,‘ ‘!=‘, ‘IN,‘ and ‘NOT IN, ‘ to query the field values. We used operators like INCLUDES and EXCLUDES to filter and get the multiselect picklist field values.
You may also like to read.
- Aggregate Query in Salesforce SOQL
- DATE Clause in SOQL
- GROUP BY Date Clause in SOQL
- Salesforce Relationship Queries in SOQL
- ORDER BY Clause in SOQL
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
