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’s the Difference Between SQL and SOQL?
What Is SOQL?
SOQL is a language used exclusively for querying the database rather than modifying data like in traditional SQL. There are no
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.
SOQL and SQL Differences
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.
SELECT * FROM Lead
This would need to be more specific in SOQL.
SELECT Id, Name, Company, Industry FROM Lead
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).
Querying a child-to-parent relationship uses simple dot notation. Let’s compare these queries, which perform the same action.
SELECT Contact.Name, Contact.Email, Account.Name FROM Contact LEFT JOIN Account ON (Contact.AccountId = Account.Id)
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.
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)
SELECT Id, Name, Account.Name, Account.Owner.Name FROM Contact
Parent-to-child relationships are queried using the child object’s plural form and a subquery within the
SELECT Account.Name, Contact.Name, Contact.Email FROM Account LEFT JOIN Contact ON (Account.Id = Contact.AccountId)
SELECT Name, (SELECT Name, Email FROM Contacts) FROM Account
Aggregate functions are used in SOQL similarly to traditional SQL. The available aggregate functions are
SUM (), and
There is a slight difference in counting all records.
SELECT COUNT(*) FROM Account
SELECT COUNT() FROM Account
COUNT_DISTINCT function lets you easily identify the number of different values for a given field.
SELECT COUNT(DISTINCT MailingCountry) FROM Contact
SELECT COUNT_DISTINCT(MailingCountry) FROM Contact
Other commonly used keywords in SQL —
OR, etc. — are supported and formatted as traditional SQL.
SELECT MailingCountry, Count(Id) FROM Contact WHERE MailingCountry IS NOT NULL GROUP BY MailingCountry HAVING Count(Id) > 1 LIMIT 10
SELECT MailingCountry, Count(Id) FROM Contact WHERE MailingCountry != NULL GROUP BY MailingCountry HAVING Count(Id) > 1 LIMIT 10
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.