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.

Example

Instead of writing this;

SELECT Id, Name FROM Account WHERE Name != NULL OR Name != ''

Write this;

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%’

Example

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

Example

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.

Example

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:

1. 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.
2. 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'

3. 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.

For Example:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource

Result:

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

1. 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.

For example:

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.

2. COUNT_DISTINCT

The COUNT_DISTINCT function returns count of values excluding null values.

For Example

SELECT COUNT_DISTINCT(Company)
FROM Lead
WHERE LeadSource = 'Web'

Note: COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL.

3. MAX

MAX function can be used to fetch the maximum value in a field.

For Example

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

4.MIN

This function returns the minimum value of a field.

For Example

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

5. SUM

This function returns the total sum of a numeric field.

For Example

SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

6. AVG

This function returns the average value of a numeric field.

For Example

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:

1. Developer Console
2. Workbench
3. Visualforce Plugins
4. Salesforce Inspector

Conclusion

There are plenty more tips and tricks that we will continue to add to this article as and when we find out more.