SOQL Cheat Sheet
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.
| Command | Description | Example |
|---|---|---|
| SELECT | SELECT 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 |
| FROM | FROM 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 |
| WHERE | WHERE is used to filter results of your queries. Multiple filters and operators can be defined. | SELECT Name FROM Account WHERE Type = 'Customer' |
| LIMIT | LIMIT 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 |
| OFFSET | OFFSET 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 BY | ORDERBY 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 |
| AND | AND 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' |
| OR | OR 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' |
| LIKE | LIKE 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%' |
| IN | IN 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') |
| GROUPBY | GROUP 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_DISTINCT | Returns 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 field | SELECT MAX(AnnualRevenue) FROM Account |
| SUM() | Returns the total sum of a numeric field | SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60 |
| AVG() | Returns the average value of a numeric field | SELECT 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 |
|---|---|
| TODAY | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = TODAY |
| YESTERDAY | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = Yesterday |
| TOMORROW | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = TOMORROW |
| THIS_WEEK | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = THIS_WEEK |
| NEXT_MONTH | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CloseDate = NEXT_MONTH |
| LAST_90_DAYS | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = LAST_90_DAYS |
| LAST_MONTH | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = LAST_MONTH |
| THIS_YEAR | SELECT Name, Amount, CloseDate FROM Opportunity WHERE CLOSEDate = THIS_YEAR |
Jurgis
You are missing FOR UPDATE 😉
boB
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
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
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
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
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
If a Soql Query returns no results, does it return null or throw an exception?
VIJAY SHARM
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
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 ?