Salesforce SOQL: Accessing your Data Made Easy

Salesforce SOQL is equivalent to the SELECT SQL statement that searches the record on a given criterion only in a single Object.

• One difference between SOSL and SOQL is that it cannot search across multiple objects but supports nested queries.
• A CRM solution helps a company to focus on the organization’s relationships with the customers which involve many important steps including finding new customers, maintaining their databases, helping them in growing their business, providing needed support, and other services.
• When it comes to customer resource management software, Salesforce is one of the most used solutions by many companies.
• It is important to be able to access and use this CRM data in an efficient manner. Salesforce SOQL lets you do just that.

Key Feature of Salesforce

• Helps you optimize the conversion rate
• Productivity enhancement of staff
• Intelligent and insightful reporting
• Visual workflows for each level of employees
• Account Management
• Lead Management
• Dashboards for business owners and workers alike

Understanding Salesforce SQL (SOQL)

• Salesforce SQL is also known as the Salesforce Object Query Language (SOQL).
• We can use SOQL to Query Salesforce Database to search for the organization’s Salesforce data for some specific information.
• Both SQL and SOQL allow you to specify a source object by using the SELECT statement. Additionally, Salesforce SQL uses filters along with the SELECT clause to return an optional set of data. 
• The return type of SOQL is always a list of <sObjects>.
• If we are making a SOQL query on an accounts object, it will return a list of accounts and if we are making a SOQL query on a contacts object, it will return a list of contacts.
• In short, if we are making a SOQL query on a custom object, it will return a list of custom objects. Moving forward, SOQL does not support advanced features of SQL like * and joins. For example, we cannot do “SELECT * ” to perform a selection of all the values. 

So now the question is when to use SOQL?

1. To retrieve data from a single object or multiple objects that are related to one another. 
2. Count the number of records that meet specified criteria.
3. Sort results as a part of the query e.g. you want the data in ascending or descending order. 
4. Retrieve data from number, date, or checkbox fields. 

Simple/Basic SOQL Structure

SELECT one or more fields, [SubQuery]
FROM an object
WHERE filter statements
GROUP BY
HAVING count(Email) > 2
ORDER BY CreatedDate ASC
LIMIT 10 
OFFSET 5
FOR {VIEW, UPDATE}

Different Types of SOQL Queries

• Child to Parent
• Parent to Child (Inner Query)
• Order By in SQL
• Group By in SOQL
• Having in SOQL 
• Aggregate Queries in SOQL
• Polymorphic Query

Child to Parent

• When you want to fetch a few fields of the parent record while working with the child record then you can go for the child to parent query.
• For example, if while working with “Contacts” records user needs the “Account Shipping Address” then for this use case, Salesforce SQL will look like this:

SELECT ID, NAME, AccountId, Account.ShippingAddress FROM Contact

In the above SOQL, “Account” is the field name for lookup/master details on the contact (child) object.    

Parent to Child (Inner Query)

• Whenever you want to retrieve the related/child record for a particular parent record or a set of parent records then you can use the concept of the inner query.
• For example, to retrieve the Contacts related to a particular Account we will be using below SOQL:

SELECT Id, Name, (Select Id, Name From Contacts) FROM Account WHERE ID IN: accList 

• In the above query, “Contacts” is the relationship name. For a standard object, it will always be the plural label of the object name. For example, 

Contact => Contacts, Case => Cases, Number => Numbers

• For custom objects, we will append “_r” which will represent the relationship. For example, if the custom object name is “Book” and it has a lookup relationship with “Account” then in the inner query, it is represented as Books_r. r represents a relationship.     

Order By in SOQL

• We can use ORDER BY in a SELECT statement of a SOQL query to control the order of query results.
• By default, the order is ascending. There is no guarantee of the order of the result unless we use ORDER By clause in the query.
• You can use the Order By clause using the following syntax:

[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]   

• For example, if you want to query data from the accounts table and sort it in the descending order for the records having NULL in the last, you can use the following query:

SELECT Name
FROM Account
ORDER BY Name DESC NULLS LAST

Few factors that affect the result obtained through ORDER BY are:- 

• It is case-sensitive.
• We can sort multiple columns by listing more than one fieldexpression clause.
• Sort order is determined by the current user locale. For example, for English locales, it uses sorting mechanisms based on the UTF-8 values of character data, and for Asian locales, it uses a linguistic sorting mechanism based on ISO 14651 & Unicode 3.5 standards.

This is how you can use the Order By clause in Salesforce SQL (SOQL).

Group By in SOQL

• Group By as the name suggests is an SQL query to avoid iterating through individual query results. In short, you specify a group of records despite processing multiple records. 
• You can use the Group By clause using the following syntax:

[GROUP BY fieldGroupByList]

• For example, if you want to count the number of leads related to each type of lead source, you can do this by using the Group By clause while querying data from the lead table as follows:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource

This is how you can use the Group By clause in Salesforce SQL (SOQL).

Having in SOQL 

• It is used to filter out results/records of an aggregate function based on a particular field. You can use the Having clause using the following syntax:

[HAVING havingConditionExpression]

• For example, if you to filter out records having an Id count of more than one or filter out duplicate records from the contact table, you can use the Having clause as follows:

SELECT Name, Count(Id)
FROM Account
GROUP BY Name
HAVING Count(Id) > 1

This is how you can use the Having clause in Salesforce SQL (SOQL).

Aggregate Queries in SOQL

An aggregate query is used to find the aggregate result for a field. Below are a few methods that are supported in an aggregate query:

• AVG(): The AVG aggregate function returns the average value for a numeric field that belongs to your table.

You can use the AVG() function using the following syntax:

SELECT AVG(NUMERIC_COLUMN) FROM TABLE;

For example, if you want to know the average amount for each campaign, you can use the following query:

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

• COUNT(): The COUNT aggregate function returns the count of records or rows that satisfy your query criteria.

You can use the COUNT() function using the following syntax:

SELECT COUNT(COLUMN) / COUNT() FROM TABLE;

For example, if you want to know the count of names that start with ‘b’, you can use the following query:

SELECT COUNT()
FROM Account
WHERE Name LIKE 'b%'

• SUM(): The SUM aggregate function returns the sum value for a numeric field that belongs to your table.

You can use the SUM() function using the following syntax:

SELECT SUM(NUMERIC_COLUMN) FROM TABLE

For example, if you want to know the sum of sale amounts for the year 2020, you can use the following query:

SELECT SUM(Amount)
FROM Sales
WHERE Year='2020'

• MAX() & MIN(): The MAX & MIN aggregate functions return the maximum and minimum values respectively for a numeric field that belongs to your table.

You can use the MAX() & MIN() functions using the following syntax:

SELECT MAX(NUMERIC_COLUMN) FROM TABLE or SELECT MIN (NUMERIC_COLUMN) FROM TABLE

For example, if you want to find the maximum budget value for each campaign, you can do this using the following query:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

This is how you can use the aggregate functions in Salesforce SQL (SOQL).

Polymorphic Query

• A Polymorphic query or relationship is the one where the current object can be one of several object types depending on a related event. 
• Example Polymorphic Query is as follows:

List<Event> events = [SELECT Description FROM Event WHERE What.Type IN ('Account', 'Opportunity')];

Execution of Salesforce SQL (SOQL) Queries

Salesforce allows you to execute your Salesforce SQL (SOQL) queries either by using the Query Editor or as part of the Apex Code.

• Executing SOQL Queries using the Query Editor
• Executing SOQL Queries using the Apex Code

Executing SOQL Queries using the Query Editor

• To start executing your SOQL queries using the Query Editor, you first need to have some data in your Salesforce database.
• For example, if you want to save the information containing the contact details of three control engineers, you can do this by clicking on debug, then the “Open Execute Anonymous Window ” option, and then adding the following lines of code:

Contact contact1 = new Contact(
   Firstname='Quentin',
   Lastname='Foam',
   Phone='(415)555-1212',
   Department= 'Specialty Crisis Management',
   Title='Control Engineer - Specialty - Solar Arrays',
   Email='qfoam@trailhead.com');
insert contact1;
// Add second contact and related details
Contact contact2 = new Contact(
   Firstname='Vega',
   Lastname='North',
   Phone='(416)556-1312',
   Department= 'Specialty Crisis Management',
   Title='Control Engineer - Specialty - Propulsion',
   Email='vnorth@trailhead.com');
insert contact2;
// Add third contact and related details
Contact contact3 = new Contact(
   Firstname='Palma',
   Lastname='Sunrise',
   Phone='(554)623-1212',
   Department= 'Specialty Crisis Management',
   Title='Control Engineer - Specialty - Radiators',
   Email='psunrise@trailhead.com');
insert contact3;

Once you have the desired information in your Salesforce database, you can now start writing SOQL queries to extract the data. For example, you want to fetch the data related to engineers who work in the Specialty Crisis Management department.

You can do this by clicking on the Query Editor tab and entering the following SOQL query:

SELECT Name, Phone, Email, Title FROM Contact WHERE (Department = 'Specialty Crisis Management')

Once you’ve written the query, click on the execute button. You will now be able to see the results of your SOQL query on your screen. You can click on the Refresh Grid option to re-execute your SOQL query.

This is how you can use the Query Editor to execute Salesforce SQL (SOQL) queries.

Executing SOQL Queries using the Apex Code

1. Another way of executing SOQL queries is by using an Apex code. Such SOQL queries are known as inline SOQL queries, and you can run them with the help of the Developer Console.
2. For example, if you want to fetch the data related to engineers who work in the Specialty Crisis Management department with the help of an inline SOQL query, you can do as follows:
3. Click on debug and then select the “Open Execute Anonymous Window” option. Once you’ve clicked on it, enter the following lines of code:

Contact[] theseContacts = [SELECT Name, Phone, Email, Description FROM Contact WHERE (Department='Specialty Crisis Management') ORDER BY Name];
// Log a count of how many contacts were found
System.debug(theseContacts.size() + ' contact(s) returned.');
// Log all values in the array of contacts
System.debug(theseContacts);

• Now click on execute. You will now be able to see the results of your query by opening the logs and selecting the “debug only” option.
• The output of your query execution, that is the contact information will be available in alphabetic order.
• This is how you can use the Apex Code to execute Salesforce SQL (SOQL) queries.

Limitations of using Salesforce SQL (SOQL)

• We can execute 100 SOQL in a single transaction. 
• Maximum records returned by a single SOQL query are 50k. 
• Maximum 20 parent-to-child relationships can be specified in a query. 
• A custom object allows upto 40 relationships. In this way, you can reference all child-to-parent relationships for custom objects in one query. 
• In each specified relationship, only one level of parent-to-child can be specified in a query. For example, if the FROM clause is specific to Account, the SELECT clause can be specific to only the Contact or other objects that can be present at that level. It will not help in specifying a child object of Contact.
• The maximum limit of SOQL is 20,000 characters  
• If characters exceed this number then we will get a QUERY_TO_COMPLICATED error. 
• Similarly, in Lightning, if a page layout contains more than 250 fields then it will result in QUERY_TO_COMPLICATED error because Lightning uses an auto-generated query to retrieve fields. 

Conclusion

• To summarize, SOQL is equivalent to the SELECT SQL statement that searches the record on a given criterion only in a single Object.
• One difference between SOSL and SOQL is that it cannot search across multiple objects but supports nested queries.
• Handling huge amounts of varied data can be a real pain. Ensuring data integrity and simplicity for analytics can be a lot of elaborate work.
• But it does not have to be a frustrating experience. Hevo Data can make your life a lot easier by transferring your data from 150+ plug-and-play connectors like Salesforce, etc., to any desired destination, without you writing a single line of code.