Get Started with SOQL Queries

Learning Objectives

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:

1. What fields do I want to display?
2. 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 (SELECT and FROM), record fields (Name and Email), and an object (the standard Contact object).

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

SELECT

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: Name and Email.

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.

FROM

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.

Yes, we really mean a brand-new Trailhead playground! If you use an existing org or playground, you can run into problems completing the challenges.

Build a Query Using the Query Editor

1. In your Trailhead Playground, click and then select Developer Console.
2. Click File | Open | Objects | Contact.
   
3. Click Open.
   The Contact object opens listing the names and types of all the fields associated with it.
4. 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.
5. Using CTRL + click (Windows) or CMD + click (Mac), select the Email, Name, and Languages__c fields.
6. 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.
7. 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.

WHERE

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.

SELECT Name, Email
FROM Contact
WHERE FirstName = 'Stella' AND LastName = 'Pavlova'

SELECT Name, Email
FROM Contact
WHERE LastName = 'James' OR LastName = 'Barr'

SELECT Name, Email FROM Contact
WHERE LastName IN ('James', 'Barr', 'Nedaerk', 'Forbes')

LIMIT

The 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.

1. Return to the Query Editor in the Developer Console.
2. At the end of the query, type LIMIT 5, so that it reads:

   SELECT Email, Name FROM Contact LIMIT 5

3. Click Execute.

The Query Results pane shows five results.

ORDER BY

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.

SELECT Name, Email FROM Contact
ORDER BY Name ASC
LIMIT 5

SELECT Name, Email FROM Contact
ORDER BY Email DESC
LIMIT 5

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 Name and Email fields from five contacts, sorted in ascending order by name.

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.

Resources

• Trailhead Live:Trail Together -SOQL for Admins
• Trailhead: Developer Console Basics
• Trailhead: Apex Basics and Database
• Salesforce Developer Documentation: SOQL and SOSL Reference