SOQL in Salesforce

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

dont miss out iconDon'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:-

  1. AND
  2. OR
  3. 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.

dont miss out iconCheck 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

Popular Salesforce Blogs