Get Started with SOQL Queries
After completing this unit, you'll be able to:
- Construct a valid SOQL query using required clauses.
- Filter query results by multiple conditions.
- Sort query results.
If this is your first stop on the Build Apex Coding Skills trail, you have come too far. Take a step back and earn the Apex Basics for Admins and Object-Oriented Programming for Admins badges before you continue with this module.
Already earned those badges? Excellent work! Now that you have a basic understanding of how to create classes, variables, and sObjects, it's time to learn about the Salesforce Object Query Language, also known as SOQL (pronounced sockle).
What Is SOQL?
SOQL is a language that gets record data from a Salesforce database. In Object-Oriented Programming for Admins you learned that you can use Data Manipulation Language (DML) statements to insert, update, and delete records. But what if you want to get data that already exists in the database? SOQL makes it simple to retrieve data.
Creating SOQL queries is similar to creating a report. When you create a report, you answer questions such as:
- What fields do I want to display?
- Where are those fields located?
You ask the same questions when you create a SOQL query. In a query, the answers to these questions are the fields and the object.
This query contains SOQL keywords (
FROM), record fields (
What's this query doing? It's retrieving (getting) specific information about contact records in the database. Let's examine how these pieces fit together and practice writing some queries. First, we look at the required parts of a SOQL query.
Required SOQL Clauses
At the foundation of any SOQL query are two clauses: the SELECT clause and the FROM clause. Think of a clause as a piece of a puzzle. It's one part of a complete query.
Every SOQL query has a SELECT clause that begins with the
SELECT keyword. The SELECT clause specifies one or more fields (separated by commas) in a Salesforce record. This example query returns two fields:
A note about field names: As an admin, when you talk to another admin, you probably refer to a field by its field label. But in code, developers use API names instead. So in your SOQL queries, always use field API names, not field labels. If you aren't sure what a field's API name is, don't worry, there are ways to figure it out. More on that later in this module.
The second clause that's required to create a SOQL query is the FROM clause.
Starting with the
FROM keyword, the FROM clause specifies an object in your Salesforce database. In this case, we want data from the
Contact object. Let's see what the data looks like when we run the query.
Follow Along with Trail Together
Want to follow along with an expert as you work through this step? Take a look at this video, part of the Trail Together series on Trailhead Live. You can find a link to the full session in the Resources section.
Ready to Get Hands-on with SOQL?
In this unit, we show you the steps to build a SOQL query in the Developer Console's Query Editor. We don't have any hands-on challenges in this unit, but if you want to follow along and try out the steps, create a new Trailhead Playground now. See the Trailhead Playground Management module to learn how. It typically takes 3–4 minutes for Salesforce to create your Trailhead Playground. You also use the playground when it's time to complete the hands-on challenges later in this module.
Build a Query Using the Query Editor
- In your Trailhead Playground, click and then select Developer Console.
- Click File | Open | Objects | Contact.
- Click Open.
The Contact object opens listing the names and types of all the fields associated with it.
- In the bottom panel, select the Query Editor tab. (If you don't see tabs, click the up arrow at the bottom of the window.)
The Query Editor has three sections. The top left section (1) displays the query. You can edit the query there. The bottom left section (2) displays errors. And the right section (3) stores a history of executed SOQL queries.
- Using CTRL + click (Windows) or CMD + click (Mac), select the Email, Name, and Languages__c fields.
- Click Query.
The SOQL query, based on the object and fields you selected, is displayed in the Query Editor. Notice that when you select a field in the Query Builder, the Query Builder automatically inserts the field API name into the SOQL query. With this tool, there's no need to memorize API names or go searching for them.
- Click Execute.
After the query is executed, the results show exactly what's requested. Plus, when you use the Query Editor, the Id is returned as a bonus. Pretty cool!
Optional SOQL Clauses
The SELECT clause and the FROM clause are required, but SOQL also has optional clauses to refine your query.
The example queries that you create in your Trailhead Playground return a fairly small number of records. The last query returned 20 records. But what if there were thousands of records? The amount of computing power needed to process that data would be substantial. Wouldn't it be nice if SOQL had a way to return a subset of all records? Actually, it does.
The WHERE clause sets the conditions that a record must match to be selected and returned. Use the WHERE clause the same way you use filters to limit the data shown in a list view or report. For example, if we're looking for a contact whose first name is Stella, we can add
WHERE FirstName = 'Stella' to the end of our query, like this:
SELECT Name, Email FROM Contact WHERE FirstName = 'Stella'
You can also define the WHERE clause to filter using more than one condition. There are multiple ways to do this using three operators: AND, OR, and IN. Let's consider some examples.
|Use AND to return records that meet two conditions. This query returns all records that have the first name Stella and the last name Pavlova.
SELECT Name, Email FROM Contact WHERE FirstName = 'Stella' AND LastName = 'Pavlova'
|Use OR to return records that meet one of two conditions. This query returns records with the last name James or the last name Barr.
SELECT Name, Email FROM Contact WHERE LastName = 'James' OR LastName = 'Barr'
|Use IN to return records that meet at least one of three or more conditions. The IN clause is commonly used to return the values of a picklist, or values from a LIST or SET. IN simplifies a query that would otherwise have many OR conditions. This query returns all records that have the last name James, Barr, Nedaerk, or Forbes.
SELECT Name, Email FROM Contact WHERE LastName IN ('James', 'Barr', 'Nedaerk', 'Forbes')
LIMIT keyword sets the maximum number of records to return. LIMIT is helpful when you're testing and don't want to wait for a query to process a large set of data. As you learn more about SOQL, you'll discover more relevant ways to avoid returning too many records, but until then, LIMIT is an easy solution. Let's try adding a limit to our query.
- Return to the Query Editor in the Developer Console.
- At the end of the query, type
LIMIT 5, so that it reads:
SELECT Email, Name FROM Contact LIMIT 5
- Click Execute.
The Query Results pane shows five results.
Now that you have a handle on the quantity of results, how can you organize those results? Use the
ORDER BY clause to sort results by the value of a specific field. Optionally, use a qualifier in an ORDER BY clause to specify ascending order (default) or descending order, just as you would in a spreadsheet. Finally, if you have many empty field values, use the NULLS qualifier to group all of the NULL values either first (default) or last.
|Returns results in ascending order
SELECT Name, Email FROM Contact ORDER BY Name ASC LIMIT 5
|Returns results in descending order
SELECT Name, Email FROM Contact ORDER BY Email DESC LIMIT 5
FIRST | LAST
|Returns null records at the beginning (NULLS FIRST) or end (NULLS LAST)
SELECT Name, Email FROM Contact ORDER BY Email NULLS LAST
In the ASC example,
SELECT Name, Email FROM Contact ORDER BY Name ASC LIMIT 5 returns the
In the NULLS example,
SELECT Name, Email FROM Contact ORDER BY Email NULLS LAST returns the name and email in ascending order (because ascending is the default sort order) by email. The records with no email address are grouped at the end.
With this introduction to the power of SOQL, you can now retrieve huge sets of data, set limits to get very specific results, and organize returned data to suit your needs. Pretty sweet.