SOQL vs SQL: Best Practices to Query Salesforce Database

Editor’s note: This article has been updated for accuracy and comprehensiveness in November 2023.

The article mainly focuses on introducing SOQL and comparing SOQL and SQL. After reading the article, readers will be able to:

1. Get familiar with Salesforce Object Query Language (SOQL).
2. Understand the similarities and differences between SOQL and SQL.
3. Understand objects used in the Salesforce database.
4. Write SOQL queries and understand how they are different from traditional SQL queries.
5. Understand the different types of SOQL queries along with examples.

We hope this article will be useful for you and will become an informative guide to the world of SQL and SOQL.

Table of contents

1. Introduction to SOQL
2. Main Differences Between SOQL and SQL
3. What Are Force.com Objects?
4. SOQL Query Syntax
5. Building Queries — SQL vs SOQL
6. Tools for Querying Salesforce Database

Introduction to SOQL

Salesforce Object Query Language (SOQL) is the language that queries your organization’s Salesforce data. Although it is very similar to SELECT statements used in the widely-used Structured Query Language (SQL), SOQL is designed specifically for the execution of SQL queries on the Salesforce platform. We can query the data using the Salesforce Workbench, DML (Data Manipulative Language) statements, etc. It allows the users to retrieve a list of fields from an object and filter the records based on specific conditions.

Main Differences Between SOQL and SQL

Salesforce SQL or SOQL is different in many aspects with respect to traditional SQL. SOQL does not support many features that SQL can provide, but the supported features are sufficient for many organizations to access and work with the data.

1. In SQL, the data is stored in database tables, whereas the data in Salesforce is stored in the form of objects.
2. SOQL is used primarily for querying the Salesforce database and retrieving the records. It does not allow data modifying statements like UPDATE, INSERT, etc. To update or insert multiple records in the Salesforce database, it needs to be done using Salesforce’s user interface or DML statements.
3. SOQL requires specific fields to be mentioned while querying the salesforce database. It does not permit fetching all the fields at once, like SELECT * which we can use in SQL. The reason behind the same is that the data fetched is stored in a multi-tenant environment. Such data is generally accessed by and shared with everyone, and queries like SELECT * are going to become a bottleneck for the environment and cause havoc for the other employees.
4. SOQL JOIN statements are different from those in SQL. In SQL, we can join any database and fields. SOQL does not support or allow arbitrary joins. It can only join those objects that are related to each other.

Benefits of SOQL over SQL

• SOQL helps in modeling the data better as the objects can be related to other objects and that can provide a better understanding of the data at hand using the Workbench.
• SOQL is used to manage the Salesforce data that is accessed by multiple tenants. It allows the users to fetch the data in a non-tolerant manner and restricts the queries that can become a bottleneck to the entire environment.

Tips and Good Practices in SOQL

• Build selective queries. A query is said to be selective when the filters used in a query are on an indexed field. This reduces the time and resource consumption to scan the database as it is on an indexed field.
• Avoid the use of Null keyword and wildcards. When the null keyword is used in the query, it executes a full database scan. Also, avoid usage of wildcards like % wildcard as they do not make use of an index.

What Are Force.com Objects?

Force.com is a PaaS (Platform as a Service) that is designed to assist developers to create powerful applications with ease. It is owned by the SaaS (Software as a Service) vendor Salesforce. It provides a database that can store your organization’s information in the form of objects. Each object consists of several fields that are analogous to the columns in a SQL database. Each record in an object is analogous to rows in a SQL database.

There are several types of objects available in the force.com platform like Standard objects, Custom objects, External objects, BigObjects, etc. But the two commonly used objects are:

1. Standard objects. These objects are predefined and are available for the users to use directly for their application. These include commonly used objects like Accounting, Contracts, Dashboard, Leads, etc.
2. Custom objects. The platform also allows the users to create objects that are specific to the organization’s purposes and requirements.

These objects can be related in the form of child-to-parent relationships and parent-to-child relationships. Only these related objects can be queried together to retrieve the data from multiple objects. A relationship between objects is established by creating a lookup or using the master-detail relationship method.

SOQL Query Syntax

As we now know that SOQL is essentially used for retrieving the records, we make use of SELECT statements along with optional clauses like WHERE, LIMIT, GROUP BY, etc.

A commonly used SOQL query is similar to the following structure:

SELECT list_of_fields [subquery]
FROM object_name
[WHERE condition_expression]
[GROUP BY list_of_fields]
    [HAVING condition_expression] 
[ORDER BY list_of_fields {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT count_of_rows_to_return]
[OFFSET count_of_rows_to_ignore]

Example:

SELECT Name,Contact
FROM Account
WHERE Name='ABCD Corporation'

The clauses used within the square brackets are optional. These are used to filter the records according to the requirements of the queries.

Types of SOQL Queries

Apart from the standard SELECT queries, SOQL queries are categorized based on their relationship between objects. Such queries are known as SOQL relationship queries.

1. Parent-to-child queries. In this query, we retrieve desired fields from the child and the object used in FROM clause is the parent. These queries are executed in the form of nested queries i.e. using a subquery in SELECT clause, and by using the plural form of the child’s object.
2. Child-to-parent queries. In this query, the object used in FROM clause is the child. To access the parent’s fields from the child, SOQL makes use of dot (.) notation. This notation allows us to retrieve the data from more than two objects.

Building Queries — SQL vs SOQL

Here we compare SQL and SOQL queries. Find examples of queries below and use them later if needed, building your own query scenarios.

SELECT

SQL:

SELECT *
FROM Lead;

SQL allows the use of wildcards like * to fetch all the fields from the table.

SOQL:

SELECT Id, Name
FROM Lead

JOINS

Child-to-parent query:

SQL:

SELECT Contact.Id, Contact.FirstName, Account.Name
FROM Contact
LEFT JOIN Account ON (Contact.AccountId = Account.Id);

SOQL:

SELECT Id, FirstName, LastName, Account.Name
FROM Contact

Here, Account is the parent object and Contact is the child object. This query will fetch the contact’s details along with the account’s name.

Parent-to-child query:

SQL:

SELECT Account.Name, Contact.Id, Contact.FirstName
FROM Account
LEFT JOIN Contact ON (Account.Id = Contact.AccountId);

SOQL:

SELECT Name, (SELECT Id,FirstName FROM Contacts)
FROM Account

In the above query, we have mentioned the ‘Contact’ object as contacts and the records of the child’s objects are fetched as a subquery.

DISTINCT VALUES

SQL:

SELECT DISTINCT Name
FROM Account;

SOQL:

SELECT Name
FROM Account
GROUP BY Name

SOQL DISTINCT requires a bit of workaround to fetch distinct values of a field as we need to make use of the GROUP BY clause. We can use COUNT_DISTINCT() to fetch the count of the distinct values in SOQL.

COUNT CHILD RECORDS

SQL:

SELECT COUNT(a.name)
FROM account a, contact c
WHERE a.contact_id=c.contact_id;

In SQL, the primary key of the parent must exist as the foreign key in another to make it a parent-child relationship. Based on that field, we can retrieve the counts in SQL.

SOQL:

SELECT AccountId, count(Id)
FROM Contact
GROUP BY AccountId

UPDATE

SQL:

UPDATE Contact
SET FirstName = 'John'
WHERE CustomerID = 1012;

SOQL:

SOQL is a query-only language. We need to use DML statements to update or insert multiple records in Salesforce Database. We can also make use of Skyvia Data Integration for importing the data into the database. To update the records using DML, we need to first fetch the records that need to be updated and then update the same.

INSERTING MULTIPLE RECORDS

SQL:

INSERT INTO Account (Name, City)
VALUES
    ('Name0', 'City0'),
    ('Name1', 'City1'),
    ('Name2', 'City2'),
    ('Name3', 'City3'),
    ('Name4', 'City4');

SOQL:

Account[] accounts = new List<account>();
for(Integer i=0;i<5;i++) {
    Account new_record = new Account(Name='Name' + i, City='City' + i);
    accounts.add(new_record);
}
Account accountToUpdate;
try {
    insert accounts;
} catch(DmlException e) {
    System.debug('An unexpected error has occurred: ' + e.getMessage());
}</account>

AGGREGATE FUNCTIONS

The usage of most of the aggregate functions is similar in both languages. SOQL has a function COUNT_DISTINCT() that retrieves the number of distinct records in the table.

SQL:

SELECT COUNT(DISTINCT Name)
FROM Contact;

SELECT Name, Count(Id)
FROM Account
WHERE AccountNumber IS NULL
GROUP BY Name
LIMIT 10;

SOQL:

SELECT COUNT_DISTINCT(Name)
FROM Contact

SELECT Name, Count(Id)
FROM Account
WHERE AccountNumber = NULL
GROUP BY Name
LIMIT 10

Tools for Querying Salesforce Database

Salesforce Workbench

Using Salesforce Workbench is one of the primary methods to query the Salesforce database. It allows the users to view the organization’s data, perform SOQL queries, etc. It is a basic tool that can help you understand the Salesforce platform and manage the data.

Skyvia Query

SQL professionals can execute queries using the SQL Mode, and SQL beginners can make use of the Builder Mode that is a drag-and-drop mode to execute queries without typing any code.Skyvia also provides users with a wide range of predefined query templates to work with Salesforce. These templates are known as public queries. You simply select a ready-made public query and automate your process in a few clicks. Additionally, you can study some aspects of the SQL language using these queries. To try it, go to the Skyvia Gallery and switch to the Queries tab.If SOQL is getting difficult for you to grasp, then Skyvia Query is the perfect tool to relieve you from all the hassle. The additional features of Skyvia include operations like mass update or mass delete that come in handy to deal with a huge amount of data and can be done without the use of DML statements.