Best SOQL Techniques and Practices
This article contains tips and tricks as well as examples of how to maximize the use of Salesforce Object Query Language. Most commonly known as SOQL!
Let’s jump straight into some guidelines to writing efficient SOQL queries using available statements.
1. WHERE Clause
Use the WHERE clause in a query to retrieve an optimized result that meets your needs and make sure you always query with positive scenarios instead of negative. Similarly try to use AND instead of OR to narrow down search results.
Instead of writing this;
SELECT Id, Name FROM Account WHERE Name != NULL OR Name != ''
SELECT Id, Name FROM Account WHERE Name = 'Some Text Keyword' AND CreatedDate = THIS_WEEK
2. LIKE Operator
Use the LIKE Clause when you want to find a particular record that starts with a certain value or contains a certain 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. It is also similar to SQL Include and Contain statements.
The syntax for a LIKE Clause is simple where % is added either before or after or both depending on whether you want any of the following use cases:
- Starts With Keyword: ‘A%’
- Ends With Keyword: ‘%A’
- Contains Keyword: ‘%Text%’
SELECT Id, Name FROM Account WHERE Name LIKE 'A%'
Note: Only a single keyword can be passed in the LIKE operator instead of multiple.
3. ORDER BY Clause
Use the ORDER BY Clause to sort the records returned by a particular field either ascending or descending. Syntax:
- ASC or blank for Ascending Order
- DESC for Descending Order
SELECT Id, Name FROM Account WHERE Name LIKE 'A%' ORDER BY Name
Note: By default ORDER BY sorts records in ascending order so no need to specify ASC for order by when needing ascending order.
4. IN and NOT IN Operators
The IN and NOT IN operators can be used to compare multiples values against a field. These are like other comparison operators such as the LIKE, =, != etc that are written in the WHERE clause statement.
The syntax of the IN and NOT IN operators is below:
- The values for IN and NOT IN must be in parentheses. String values must be surrounded by single quotes.
SELECT Name FROM Account WHERE BillingState IN ('California', 'New York')
SELECT Name FROM Account WHERE BillingState NOT IN ('California', 'New York')
Note: The IN and NOT IN operators can be used to compare values in a list against a field. Such as:
SELECT Id, Name FROM Account WHERE ID IN: Ids
5. NESTED Query/SOQL Subquery
SOQL provides an ability to query multiple relationship query types. Relationship queries are not the same as SQL joins. You must have a relationship between objects to create a join in SOQL.
These are listed with examples below:
- Parent to Child – This is one of the more common type of query that can provide us with related data records. The main query is written on the Parent level i.e. The first SELECT and FROM reference data from the parent object. The second SELECT and FROM reference data from the child object which is added in parenthesis () as a subquery . The records returned contain
SELECT Name, ( SELECT FirstName FROM Contacts ) FROM Account
- Note that for standard object subqueries, you should specify the plural name of the object as that is the name of the relationship for each object.
- Child to Parent – The ability to fetch parent records field data from the child record is one of the best features of SOQL. Especially with the syntax being as simple as added a dot ‘.‘ after the parent objects’ name. You can go 2-3 levels deep to fetch information from a parents’ parent record too.
SELECT Id, Name, Account.Name FROM Contact WHERE Account.RecordType.Name = 'Partner'
- Combination – SOQL allows an ability to query records from an object and its parent or children related objects.
SELECT Name, ( SELECT CreatedBy.Name FROM Notes ) FROM Account
6. GROUP BY
The SOQL Group By option is similar to the Distinct option in SQL. It allows a user to specify how a number of records can be grouped by a field.
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
|Word of Mouth||100|
Aggregate SOQL Functions
We have collected a list of aggregate functions available on SOQL. Let’s first see the most useful function, in our opinion i.e. COUNT
Count is one of the easiest way to determine number of records that meets a specific criteria. Running a SOQL query is definitely a faster method for Salesforce Admins and Developers to quickly get an understanding of how many records may meet a certain criteria.
SELECT Count(ID) FROM Account WHERE Industry = 'Media' AND Type = 'Partner'
Note the following when using COUNT():
- COUNT() must be the only element in the SELECT list.
- The number of rows returned byCOUNT() includes null values that match the filtering conditions of the query.
- You can use COUNT() with a LIMIT clause.
- You can’t use COUNT() with an ORDER BY clause. Use COUNT(fieldName) instead.
- You can’t use COUNT() with a GROUP BY clause for API version 19.0 and later. Use COUNT(fieldName) instead.
The COUNT_DISTINCT function returns count of values excluding null values.
SELECT COUNT_DISTINCT(Company) FROM Lead WHERE LeadSource = 'Web'
Note: COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL.
MAX function can be used to fetch the maximum value in a field.
SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name
This function returns the minimum value of a field.
SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName
This function returns the total sum of a numeric field.
SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
This function returns the average value of a numeric field.
SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId
SOQL Date Literals and Comparison
For example, you could use the CALENDAR_YEAR() function to find the sum of the Amount values for all your opportunities for each calendar year.
SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_YEAR(CreatedDate)
The full list of date functions can be reviewed here.
SOQL Query Tools
There are plenty of ways to query data in Salesforce. While queries are very useful to get data records in apex code, it also helps to run query on a UI tool to allow salesforce admins and developers to query data quickly to review for testing or investigation.
The following tools can be used to query Salesforce data from the User Interface:
- Developer Console
- Visualforce Plugins
- Salesforce Inspector
There are plenty more tips and tricks that we will continue to add to this article as and when we find out more.