Differences Between SOQL and SQL Explained

Salesforce object query language (SOQL) is a language used to query data from your Salesforce organization. Software engineers or analysts with a background in SQL may find SOQL to be syntactically familiar, but there are a few key differences to be aware of before getting started.

What Is SOQL?

SOQL is a language used exclusively for querying the database rather than modifying data like in traditional SQL. There are no INSERT or UPDATE statements. Changing data is done using Salesforce’s user interface (UI) or Apex DML, part of Salesforce’s proprietary programming language. In SOQL, Salesforce objects are represented as SQL tables.

More on Data Science: SQL vs NOSQL: Which Should You Choose?

SOQL and SQL Differences

SELECT

The SELECT clause of a SOQL query is similar to SQL with one significant exception: SELECT * is not allowed; you must specify the fields to select. Let’s look at example queries in SQL and SOQL.

SQL:

SELECT * FROM Lead

This would need to be more specific in SOQL.

SOQL:

SELECT Id, Name, Company, Industry FROM Lead

JOIN

The most significant difference when using SOQL is its handling of joins. The JOIN keyword does not actually exist in SOQL. Additionally, only related objects can be queried together.

Parent-to-child or child-to-parent relationships are the most common and straightforward relationships in Salesforce. We will demonstrate the concept of SOQL “joins” using the relationship between account (parent) and contact (child).

Child-to-Parent

Querying a child-to-parent relationship uses simple dot notation. Let’s compare these queries, which perform the same action.

SQL:

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

SOQL:

SELECT Name, Email, Account.Name FROM Contact

We can even traverse several relationships using this dot notation; the equivalent of joining more than two tables.

SQL:

SELECT Contact.Name, Contact.Email, Account.Name, User.Name 
FROM Contact 
LEFT JOIN Account ON (Contact.AccountId = Account.Id)
LEFT JOIN User ON (Account.OwnerId = User.Id)

SOQL:

SELECT Id, Name, Account.Name, Account.Owner.Name FROM Contact

Parent-to-Child

Parent-to-child relationships are queried using the child object’s plural form and a subquery within the SELECT clause.

SQL:

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

SOQL:

SELECT Name, (SELECT Name, Email FROM Contacts) FROM Account

Aggregate Functions

Aggregate functions are used in SOQL similarly to traditional SQL. The available aggregate functions are COUNT(), MIN (), MAX (), AVG (), SUM (), and COUNT_DISTINCT().

There is a slight difference in counting all records.

SQL:

SELECT COUNT(*) FROM Account

SOQL:

SELECT COUNT() FROM Account

And the COUNT_DISTINCT function lets you easily identify the number of different values for a given field.

SQL:

SELECT COUNT(DISTINCT MailingCountry) FROM Contact

SOQL:

SELECT COUNT_DISTINCT(MailingCountry) FROM Contact

Other Keywords

Other commonly used keywords in SQL — WHERE, LIMIT, OFFSET, GROUP BY, HAVING, LIKE, AND, OR, etc. — are supported and formatted as traditional SQL.

SQL:

SELECT MailingCountry, Count(Id)
FROM Contact
WHERE MailingCountry IS NOT NULL
GROUP BY MailingCountry 
HAVING Count(Id) > 1
LIMIT 10

SOQL:

SELECT MailingCountry, Count(Id)
FROM Contact
WHERE MailingCountry != NULL
GROUP BY MailingCountry 
HAVING Count(Id) > 1
LIMIT 10

More on Data Science: 10 Best SQL Editors in the Market

How to Query in SOQL

There are a few different options for writing SOQL queries. You can use the developer console query editor within Salesforce, Salesforce Workbench or inline SOQL within Apex code. I prefer Workbench over query editor because it displays all queryable objects and fields, and it allows you to quickly export results to a CSV file. Workbench also provides the option to write queries declaratively, which is helpful for beginners without much SQL experience.