SOQL (Salesforce Object Query Language)

 

What is SOQL?

SOQL stands for Salesforce Object Query Language. It is a SQL-like language that queries records from Salesforce objects like Accounts, Contacts, Opportunities, etc. SOQL allows us to retrieve data that matches specific criteria. 

Some of the key benefits of SOQL are:

• It provides an easy mechanism to extract relevant data from Salesforce objects. 
• SOQL queries can be embedded in Apex classes and Visualforce pages to display data on the front end.
• Aggregate functions can be used to summarize data. 
• Query results can be limited, sorted, and filtered as per requirements.
• SOQL is optimized for the Salesforce multi-tenant architecture. Queries are fast and efficient.

SOQL Query Structure and Syntax 

The basic structure of a SOQL query is

SELECT <field list>
FROM <object name>
WHERE <condition(s)>
LIMIT <number of rows>

For example, to query Account records where the Account Name starts with ‘Acme’, the SOQL query would be:

SELECT Name, BillingCountry 
FROM Account
WHERE Name LIKE 'Acme%'
LIMIT 10

This will return the Name and BillingCountry of 10 Accounts where the Name starts with ‘Acme’.

Different Parts of a SOQL Query 

SOQL has several parts, some of them are:

• SELECT
  Specifies the list of fields to retrieve from the object. We can select standard fields, custom fields, and aggregate functions.

• FROM
  Specifies the object to query. We can query standard objects like Account, Contact, Opportunity, or custom objects. 

• WHERE
  Filters the records to retrieve only those that match the specified conditions. Conditions can include parameters like =, <>, >, <, >=, <=, LIKE, IN, etc.

• LIMIT
  Restricts the number of records retrieved to the specified limit. This helps improve query performance. 

• ORDER BY
  Sorts the retrieved records in ascending or descending order based on the specified fields. The default sort order is ascending.

• GROUP BY
  Groups query results based on the specified field. Usually used in conjunction with aggregate functions.

• HAVING
  Filters records after grouping and aggregation. Similar to WHERE but operates on grouped records.

Using SOQL to Query Salesforce Standard and Custom Objects

We can use SOQL to query both standard Salesforce objects as well as custom objects. The syntax is the same, we just need to specify the correct object name.

For example, to query Accounts, the object name is simply ‘Account’:

SELECT Name, BillingCountry
FROM Account
WHERE Name LIKE 'Acme%' 

To query the Opportunity object, use:

SELECT Id, Name, StageName, Amount
FROM Opportunity
WHERE CloseDate = THIS_MONTH  

For custom objects, refer to them by their API name. For a custom object with the API name ‘Product__c’, the SOQL would be: 

SELECT Name, Description__c, Price__c
FROM Product__c
WHERE Name = 'Widget'

We can query fields of all data types including text, number, date, lookup relationship, etc. SOQL also allows querying across relationships using dot notation. For example, to retrieve the Account Name for Opportunity records, we can use:

SELECT Opportunity.Name, Account.Name
FROM Opportunity

This works for both standard object relationships as well as custom object relationships.

Want to make a career in the Salesforce industry? Make sure to enroll in a Salesforce Training Course right now!

Limiting Results and Sorting with SOQL  

To optimize performance, limiting the number of records retrieved by a SOQL query using the LIMIT clause is a good practice. For example, to retrieve only the first 50 Account records, we can use:

SELECT Id, Name
FROM Account
LIMIT 50

To sort the retrieved records in ascending or descending order, use the ORDER BY clause. For example, to sort Accounts by Name in descending order:

SELECT Id, Name
FROM Account
ORDER BY Name DESC

We can sort based on both standard and custom fields. To sort on multiple fields, specify them in the desired sort order:

SELECT Id, Name, BillingCountry
FROM Account
ORDER BY Name DESC, BillingCountry ASC

This will first sort records by Name in descending order. For records with the same Name, they will be sorted by BillingCountry in ascending order.

Using SOQL Aggregates to Summarize Data  

Aggregate functions allow us to summarize data and calculate metrics like count, sum, average, etc. Some commonly used aggregate functions in SOQL are:

• COUNT(): Returns the total number of records. 
• SUM(): Calculates the sum of values for a numeric field. 
• AVG(): Returns the average value of a numeric field. 
• MIN(): Returns the minimum value for a field. 
• MAX(): Returns the maximum value for a field. 

For example, to get the count of Accounts:

SELECT COUNT()
FROM Account

To get the sum of Amount for all Opportunities:

SELECT SUM(Amount)
FROM Opportunity  

We can also use GROUP BY to summarize data by a particular field. For example, to get the count of Accounts by BillingCountry:

SELECT BillingCountry, COUNT()
FROM Account
GROUP BY BillingCountry

The HAVING clause is used to filter summarized records. For example, to get the sum of Amount for Opportunities where the sum is greater than $200,000: 

SELECT SUM(Amount)
FROM Opportunity
GROUP BY CloseDate
HAVING SUM(Amount) > 200000

SOQL queries with aggregates can also contain standard fields, custom fields and be limited and sorted. They provide a powerful way to analyze and summarize your Salesforce data.

Check out Salesforce Tutorial to learn Salesforce!

Embedding SOQL Queries in Apex and Visualforce pages

SOQL queries are typically executed in:

• Developer Console: To test and debug SOQL queries during development. 
• Apex classes: SOQL queries can be embedded in Apex to retrieve data and use it for processing. The results are stored in Apex variables, which can be manipulated afterwards.
• Visualforce pages: SOQL queries embedded in Visualforce pages can be used to display data and create interactive reports and dashboards. 

For example, here is an Apex class that executes a SOQL query and iterates over the results:

public class AccountQuery {
  public List<Account> queryAccounts(){
    List<Account> accounts = [SELECT Id, Name FROM Account LIMIT 10];
    for(Account a : accounts){
      System.debug(a.Name);
    }
    return accounts;
  }
}

In Visualforce, a SOQL query can be used to populate a table as follows:

<apex:page>
<apex:form>
  <apex:pageBlock>
    <apex:pageBlockTable value="{!accounts}" var="a">
      <apex:column value="{!a.Name}"/>
      <apex:column value="{!a.BillingCountry}"/> 
    </apex:pageBlockTable>
  </apex:pageBlock>
</apex:form>
<apex:includeScript value="{!$Resource.AccountsQuery}"/> 
</apex:page>

<apex:includeScript> is used to execute the Apex class that returns the Query results. These results are then displayed in the Visualforce page using <apex:pageBlockTable>.

SOQL and SOSL have several differences between them. Some of them are:

Differentiation	SOQL	SOSL
Syntax	SOQL uses a SQL-like syntax for querying data from a single object or related objects.	SOSL uses a text-based syntax for searching across multiple objects and fields.
Functionality	SOQL is best suited for retrieving specific records and related data from a single object or a set of related objects.	SOSL is designed for searching across multiple objects and fields, making it ideal for complex search queries.
Query performance	SOQL is optimized for retrieving a small number of records and has better performance for retrieving data from a single object.	SOSL is optimized for searching across multiple objects and fields and can retrieve a large number of records more efficiently.
Sorting and filtering	SOQL supports the sorting and filtering of records based on specific fields.	SOSL supports the sorting and filtering of records based on relevance.

Conclusion

Salesforce SOQL is an incredibly powerful tool that allows you to extract and manipulate data from your Salesforce database. With its intuitive syntax and documentation, learning SOQL is easier than you might think. It is a must-know tool for anyone working with Salesforce data.