×

SOQL vs SQL: Best Practices to Query Salesforce Database

November 18, 2021

Prathamesh Thakar

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 the 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 the 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 * that 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

    SOQL SELECT statements require field names to be mentioned explicitly. It does not allow the use of * wildcard.

  • JOINS
    1. 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.

    2. 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();
    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());
    }
  • 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
Free online SQL query builder
Query your cloud and on-premise data from web browser via visual query builder or SQL

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

    A Salesforce Workbench alternative that suffices all the needs of the end-users is Skyvia Query Tool. It is a universal data tool that can connect with any data source and access the data. It features an easy-to-use UI for the end-users to view the data or execute SQL queries seamlessly.

    Querying Salesforce

    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.

Comments

Loading Comments