Developers / Admins

SOQL Cheat Sheet

By Ben McCarthy

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

The Author

Ben McCarthy

Ben is the Founder of Salesforce Ben. He also works as a Non-Exec Director & Advisor for various companies within the Salesforce Ecosystem.

Comments:

    Jurgis
    August 25, 2015 9:48 am
    You are missing FOR UPDATE ;)
    boB
    August 30, 2015 7:28 pm
    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...
    boB
    August 30, 2015 7:28 pm
    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...
    Timothy
    September 03, 2015 9:21 am
    You should add in NULLS LAST and NULLS FIRST (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm)
    Prerak Mody
    March 02, 2017 10:23 am
    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: ','
    Anthony
    May 01, 2017 1:18 am
    Thanks for the SOQL Cheat Sheet Ben. I would also add "INCLUDES " to the WHERE clause to cater for a picklist query. SELECT ... FROM ... WHERE Picklist_field__c INCLUDES ('VALUE')
    Matt
    January 05, 2018 5:18 pm
    If a Soql Query returns no results, does it return null or throw an exception?
    VIJAY SHARM
    June 06, 2018 4:45 pm
    How can i write query like below in SOQL SELECT Today AS Column1,0 As Column2,Id,Account_Manager__c,Account_Name__c,Address_Standardization_Notes__c,Adjacent_County__c,AMC_License_Expiration_Date__c,AMC_License_Number__c ,AS_IS_EMV__c ,As_Repaired_EMV__c ,Asset_Id__c ,ATIV_Invoice_Amount__c ,AVM_Bathrooms__c ,AVM_Bedrooms__c ,AVM_Confidence_Score__c ,AVM_FSD__c ,AVM_GLA__c ,AVM_Lot_Size__c ,AVM_Market_value__c ,AVM_Report_Date__c ,AVM_Type__c ,AVM_Year_Built__c ,Batch_Order_ID__c ,Billable_Day_of_week__c ,Borrower_1__c ,Borrower_1_Name__c ,Borrower_2__c ,Borrower_2_Name__c ,Borrower_Pre_Payment__c ,Bulk_Order_ID__c ,Cancel_Date__c ,Cancel_TAT__c ,Cancel_TAT_w_o_Hold__c ,Cancellation_Code__c ,Cancellation_Notes__c ,Cancellation_Reason__c ,CASESAFEID__c ,Channel__c ,City__c ,Client__c ,Client_Approved_Due_Date__c ,Client_Approved_SLA__c ,Client_Billable_Date__c ,Client_Code__c ,Client_Due_Date__c ,Client_Intended_User__c ,Client_Lender_Invoice_Amount__c ,Client_Order_Number__c ,Client_Order_Number_2_2__c ,Client_Order_Number2__c ,Client_Order_Number3__c ,Client_Revision_Closed__c ,Client_Revision_Request__c ,Client_Revision_Started__c ,Client_SLA__c ,ClientLender_Invoice_Amount__c ,ClientRevisionEOR__c ,Comments__c ,Compass_Record_ID__c ,Completion_Month__c ,Completion_Quarter__c ,Completion_Year__c ,Cost_to_Cure_Repair_Cost__c ,Count__c ,County__c ,CreatedById ,CreatedDate ,Custom6__c ,Date_Report_Mailed__c ,Delay_Code__c ,Duplicate_Found__c ,Enterprise_Request_Number__c ,Estimated_Client_Delivery__c ,Exceptions__c ,Exterior_Inspection_Completed_Date__c ,FHA_Case_Number__c ,First_BPO_As_Is_Amount__c ,First_BPO_Date__c ,First_BPO_Repair_Amount__c ,First_BPO_Valuation_Type__c ,FNC_Report_Fields__c ,FNC_Status__c ,FNC_Webform_URL__c ,FolderNumber__c ,Invoice_Client__c ,Invoice_Client_Full_Fee__c ,Invoice_Client_Partial_Amount__c ,Invoice_Client_Partial_Fee__c ,Invoice_Client_Trip_Fee__c ,Ion_Direct_Client_ID__c ,ION_Direct_Ref_No__c ,IsDeleted ,isIonDOrderCreated__c ,LastActivityDate ,LastModifiedById ,LastModifiedDate ,LastReferencedDate ,LastViewedDate ,Legal_Description__c ,Lien_Position__c ,Loan_Purpose__c ,Loan_Type__c ,Main_Product__c ,Main_Product2__c ,Manual_Entry__c ,Master_Latest_Completion_Date__c ,Master_Latest_Completion_Date_Report1__c ,Master_Original_Completion_Date__c ,Name ,On_Hold_Notes__c ,On_Hold_Reason__c ,Order_Addr__Latitude__s ,Order_Addr__Longitude__s ,Order_Contact_Email__c ,Order_Number__c ,Order_Placed_Date__c ,Order_Quality_Score__c ,Order_Status__c ,Order_Upgraded__c ,Order_Upgraded_Date__c ,Order_Wizard_Reason__c ,Order_Wizard_Reason_Comments__c ,Order_Wizard_Status__c ,Order_Wizard_Status_Color__c ,OrderOnHold__c ,Original_TAT__c ,OriginalTAT_w_o_Hold__c ,OwnerId ,Payment_Amount__c ,Payment_Auth_ID__c ,Payment_Date__c ,Payment_Method__c ,Payment_Notes__c ,Payment_Record_ID__c ,Payment_Status__c ,Potential_Duplicate__c ,Prev_Order_Recordtypeid__c ,Prev_Order_Status__c ,Pricing_Tier__c ,Product_code__c ,Product_Family__c ,RDP_Dispute_Count__c ,RDP_Has_Been_Disputed__c ,RecordTypeId ,Related_County__c ,Result_of_Upgrade__c ,Revision_Identifiers__c ,Rush_Order__c ,Second_BPO_As_Is_Amount__c ,Second_BPO_Date__c ,Second_BPO_Repair_Amount__c ,Second_BPO_Valuation_Type__c ,Service_Name__c ,Servicer_Code__c ,sfxId__c ,Special_Instructions__c ,State__c ,Street__c ,Subject_Property__c ,Suite_Apartment__c ,SystemModstamp ,TAT_Start_Date__c ,TempOwnerID__c ,Test_Order__c ,Total_Borrower_Prepay__c ,Total_Hold_Time__c ,Total_order_Fee_Sum__c ,Total_TAT__c ,Total_TAT_w_o_Hold__c ,Upgraded_Enterprise_Order_Number__c ,URLSrc__c ,Vendor__c ,Vendor_Due_Date__c ,Vendor_in_Neighboring_County__c ,Zip_Code__c FROM Order__c LIMIT 100;
    Kavya
    June 09, 2020 9:48 am
    Hello!! I have a requirement to search Accounts based on name: 1. Search only after the user has entered 3 or more charterers 2. Search on Name starts with Searchtext Can you please help me how to achieve this ?

Leave a Reply