SOQL Cheat Sheet

blog-soql-3-SOQL or Salesforce Object Query Language is used to search your organization’s data for specific information. SOQL can be embedded in Apex code and various other places in your Org to grab specific information in a programmatic way. You can look at SOQL as having the same function as a Salesforce report, but instead of dragging and dropping fields and values into the report, you are writing a Query to perform the same function but without the visual interface. SOQL Queries can go beyond reports in terms of function to grab more complex sets of data.

This SOQL Cheat Sheet is designed to be a quick reference guide to some of SOQL’s most common functions. If you are unfamiliar to SOQL and wish to learn more, my friend Kieren Jameson over at Woman Code Heroes has a fantastic 3 part guide that will walk you through how to get started.

CommandDescriptionExample
SELECTSELECT is the first command you will need in any SOQL statement and is used as a prefix for returning fields from a dataset, it is required always. Multiple fields can be selected by using a comma to separate them. SELECT Name, Type, BillingCountry FROM Account
FROMFROM is the second command you will need in any SOQL statement and is used as a prefix for which dataset you wish to query fields on. This is a required command and you can only query one dataset at a time.SELECT Name, Type, BillingCountry FROM Account
WHEREWHERE is used to filter results of your queries. Multiple filters and operators can be defined. SELECT Name FROM Account WHERE Type = 'Customer'
LIMITLIMIT is used to define how many records you wish to pull. If no LIMIT Is used, SOQL will pull every record available. SELECT Name FROM Account WHERE Type = 'Customer' LIMIT 5
OFFSETOFFSET is used to skip rows at the start of a query. If you are using multiple queries with LIMIT, this is a great way to make sure you are not repeating values.SELECT Name FROM Account WHERE Type = 'Customer' OFFSET 5
ORDER BYORDERBY is used to sort your query results. Results can be sorted in ascending or descending by field. They can also be sorted by null values first or last. SELECT Name FROM Account ORDER BY AnnualRevenue DESC
ANDAND is a logical operator which is used with the WHERE command to define multiple fields to filter on. The query will return records where both of these filters are true.SELECT Name FROM Account WHERE Type = 'Customer' AND BillingCountry = 'USA'
OROR is also a logical operator which is used with the WHERE command to define multiple fields to filter on. The query will return records where either of these filters are true.SELECT Name FROM Account WHERE BillingCity = 'London' OR BillingCountry = 'USA'
LIKELIKE provides a mechanism for matching partial text strings and includes support for wildcards. % is used as a wildcard to match zero or more characters and _ must match exactly one character. The example will return all emails that have force.com and salesforce.com. SELECT FirstName, LastName, Email FROM Contact WHERE Email LIKE '%force.com%'
ININ allows you to filter results with the WHERE clause against a list of values. E.g. WHERE BillingState IN ('California', 'New York'). But we can also insert another query into this command to get our list.SELECT Name FROM Account WHERE Id IN (SELECT AccountID FROM CustomObject__c WHERE Type__c = 'Customer')
GROUPBYGROUP BY is used with aggregate functions to summarise data and roll up query results instead of having individual records.SELECT LeadSource, COUNT(Company) FROM Lead GROUP BY LeadSource
COUNT()Used to count the number of rows matching the query criteria.SELECT COUNT(Id) FROM Account
COUNT_DISTINCTReturns the number of distinct non-null field values matching the query criteria SELECT COUNT_DISTINCT(Company) FROM Lead
MIN()Returns the minimum value of a field SELECT MIN(AnnualRevenue) FROM Account
MAX()Returns the maximum value of a fieldSELECT MAX(AnnualRevenue) FROM Account
SUM()Returns the total sum of a numeric fieldSELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
AVG()Returns the average value of a numeric fieldSELECT AVG(Amount) FROM Opportunity WHERE Type = 'New Customer'

 

Date Literals

Instead of defining a static date, dynamic values can be used to make sure the query is correct no matter what the date is. Here the query will automatically insert today’s date. This is called a Date Literal. Most of the below Dates can be interchanged e.g. LAST_WEEK, LAST_MONTH, LAST_YEAR. See here for a full list of Salesforce Date Literals.

Command Example
TODAYSELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = TODAY
YESTERDAYSELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = Yesterday
TOMORROW SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = TOMORROW
THIS_WEEKSELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = THIS_WEEK
NEXT_MONTHSELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = NEXT_MONTH
LAST_90_DAYSSELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = LAST_90_DAYS
LAST_MONTHSELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = LAST_MONTH
THIS_YEARSELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = THIS_YEAR

Subscribe To The Monthly Newsletter

No Spam. No Rubbish. Just great content from the Salesforce Industry.

You have Successfully Subscribed!

5 thoughts on “SOQL Cheat Sheet

    1. Don’t forget use of the “NOT” operator. Sub-selects are important too. Would be great if Salesforce had the Distinct attribute for field selection, but alas…

    2. Don’t forget use of the “NOT” operator. Sub-selects are important too. Would be great if Salesforce had the Distinct attribute for field selection, but alas…

  1. I have a question for IN clause
    1. What the appropriate format for such a query:
    SELECT Id,ContactId,OpportunityId FROM OpportunityContactRole WHERE (ContactId,OpportunityId) IN ((‘somecontactid1′,’someoppid1’), (‘somecontactid2′,’someoppid2’))
    2. I have tried the above query and it return an error – unexpected token: ‘,’

Add Comment