All You Need to Know About SOQL in Salesforce
SOQL
- SOQL stands for Salesforce Object Query Language.
- Similar to SQL, but SOQL is designed especially for Salesforce data.
- The structure and keywords of SOQL are the same as it is used in SQL.
- In Salesforce, SOQL can be used to query data in:-
- Apex Code
- Developer Console (Query Editor)
- Salesforce REST and SOAP APIs
- SalesforceCLI
Syntax of a Basic SOQL Statement:-
SELECT field List [subquery….]
FROM object
[WHERE conditions]
ORDER BY fieldOrderByList {ASC | DESC} [NULL {FIRST | LAST}][LIMIT number of rows to return]
[OFFSET number of rows to skip
For example:
SELECT Id, Name, CloseDate, Amount FROM opportunity WHERE Amount > 100000 ORDER BY Amount DESC LIMIT 5
Don't forget to check out: Learn About Query Languages & DML in Salesforce
After doing a query with the above code in the query editor of the developer console, we get the list of 5 opportunities with their Id, Name, CloseDate and Amount in which you can see the amount is greater than 100000 and it is listed in descending order.
- You can do the following changes in the above code to get in return only record name matches.
WHERE Name = United nation
It will return the record having the name United nation only.
- You can do the following changes to get in return a record with the name starting with ‘united’.
WHERE Name LIKE ‘United%’
- You can do the following changes to get in return a record having the name ‘Refinery’ in it.
WHERE Name LIKE ‘%Refinery%’
- You can do the following changes to get in return a record with the name lasting with ‘Oil’.
WHERE Name LIKE ‘%Oil’
Conditional Operators:-
SOQL support the following conditional operators:-
- = :Equals
- != :Not Equals
- < , <= :Less than , Less than or equal to
- >, >= :More than, More than or equal to
- INCLUDES , EXCLUDES :Can apply only to multi-select picklists
- LIKE : Return the match pattern
- IN : Return matching keywords after IN
- NOT IN : Return the record which does not match the keywords after NOT IN
- Each conditional operator mentioned above returns a TRUE/FALSE value.
Make your own logic by adding multiple conditional operators using the following logical operators:-
- AND
- OR
- NOT
For Example:- (using IN)
WHERE Account_Name_c IN (‘Edge comm.’ , ‘Genepoint’)
The above code will return only records having ‘Edge comm.’ and ‘Genepoint’ in the account name.
Check out another amazing blog by Anshu Raj here: Business Hours And Holidays In Salesforce
Aggregate Queries:-
- COUNT
- SUM
- AVERAGE
- MIN
- MAX
Specify the field name to perform aggregate against that field in the result set.
Let us take more examples:-
- Example of COUNT:-
SELECT COUNT (Id) FROM opportunity
WHERE CloseDate = LAST_YEAR
- Child to parent query:-
SELECT Name, Account. Name, Account. Type FROM opportunity
- Parent to child query:-
SELECT Name, (SELECT Id, Name, Type, CloseDate FROM opportunities)
FROM Account
Responses