SOQL (Salesforce Object Query Language) is used to search your Salesforce data for specific information. Here are some commonly used SOQL statements you should keep as an SOQL Cheat Sheet for quick reference.
What is SOQL in Salesforce?
SOQL Stands for Salesforce Object Query Language. It is similar to SQL but designed specifically for Salesforce data. It uses the same structure and keywords as SQL. You can query data from Salesforce using SOQL.
SOQL Cheat Sheet
Here are some statements that you shouldn’t miss:
Tips: Writing SQL Statements
- SOQL statements are not case-sensitive.
- SOQL statements can be on one or more lines.
| Command | Description | Example |
| SELECT | SELECT identifies the columns to be displayed. | Exp: Get data from the Account object. SELECT AccountNumber, Unique_ID__c, BillingCity FROM Account |
| FROM | FROM identifies the table containing those columns. | SELECT AccountNumber, Name FROM Account |
How to fetch all fields in Salesforce, like Select * From Account?
SELECT FIELDS(ALL) FROM Account LIMIT 200
TYPEOF Working with Polymorphic Relationships in SOQL Queries.
Restricting and Sorting Data
| Command | Description | Example |
| WHERE | Restricts the query to rows that meet a condition | Exp: Get All accounts where the billing city is New York SELECT Name, BillingCity FROM Account WHERE billingCity = ‘New York’ |
| ORDER BY | Sorting rows by using the ORDER BY clause | SELECT Name, NumberOfEmployees FROM Account ORDER BY NumberOfEmployees |
Conditional Expressions
Like
– The % and _ wildcards .The % wildcard matches zero or more characters.
– The _ wildcard matches exactly one character.
Learn SOQL Best practices.
Logical Operators
Logical operators can be used in the field expression of the WHERE clause in a SOQL query. These operators are AND, OR, and NOT.
| Command | Description | Example |
| AND | Returns TRUE if both component conditions are true | |
| OR | Returns TRUE if either component condition is true | |
| NOT | Returns TRUE if either component condition is false |
Aggregate Functions in SOQL
| Command | Description | Example |
| COUNT() | Count the number of records | SELECT count() FROM Account |
| MIN() | Returns the minimum value of a field | SELECT MIN(Amount) FROM Opportunity |
| MAX() | to get the maximum value of a field | SELECT MAX(Amount) FROM Opportunity |
| SUM() | Returns the total sum of a numeric field | SELECT SUM(Amount) FROM Opportunity |
| AVG() | it will return the average value of a numeric field. | SELECT AVG(Amount) FROM Opportunity |
Grouping, Filtering
| Command | Description | Example |
| GROUP BY | Use the GROUP BY clause to group the result set based on the field specified. | Exp: Get a count of accounts based on the Industry. SELECT Industry, COUNT(Name) FROM Account GROUP By Industry |
| HAVING | HAVING is an optional clause used to filter the results that an aggregated function returns | Exp: Get a count of accounts based on the Industry and where we have more than one record. SELECT Industry, COUNT(Name) FROM Account GROUP BY Industry HAVING COUNT(Name) > 1 |
Learn more about HAVING.
Limit and Offset in SOQL
| Command | Description | Example |
| Limit | maximum number of rows to return | Exp: Get 100 Account record SELECT Name FROM Account LIMIT 100 |
| Offset | You can display the results in multiple pages by using the OFFSET clause on a SOQL query. | SELECT Name, Id FROM Merchandise__c ORDER BY Name OFFSET 100 |
Date Literals
| Command | Description | Example |
| TODAY | Starts at 12:00:00 AM on the current day and continues for 24 hours. | SELECT Id FROM Account WHERE CreatedDate > TODAY |
| YESTERDAY | Day before the current day and continues for 24 hours | SELECT Id FROM Account WHERE CreatedDate = YESTERDAY |
| TOMORROW | Day after the current day and continues for 24 hours | SELECT Id FROM Account WHERE CreatedDate = TOMORROW |
| THIS_WEEK | first day of the current week and continues for seven days | SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK |
| LAST_90_DAYS | 90 days before the current day and continues up to the current second | SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS |
| LAST_MONTH | the first day of the current month and continues for all the days of that month | SELECT Id FROM Account WHERE CreatedDate = LAST_MONTH |
| THIS_YEAR | January 1 of the current year and continues through the end of December 31 of the current year | SELECT Id FROM Account WHERE CreatedDate = THIS_YEAR |
Learn more.
SOQL Crash course
Check the Crash course to learn more about SOQL and relationship queries.
Summary
Learn more about SOQL & DML in Salesforce. Hope this helps! Let me know if you have any other questions.
