Cooking With Code: A Sweet Intro to SOQL (Part One)

Welcome back to another episode of “Cooking with Code” where I introduce delectable bite-sized topics related to coding in Salesforce.

Today we are getting into a sweet subject that I can’t wait to share with you! This is the first of three posts where together we’ll tackle the basics of SOQL, or Salesforce Object Query Language. SOQL is used to pull data sets from Salesforce and can be used in both Apex and Visualforce. Fantastic stuff for Salesforce Admins to learn, because we’re all Data Geeks at heart. (Data Nerds Unite!)

In this first post, I’ll introduce the basics of SOQL statements, including these concepts and keywords: SELECT, FROM, LIMIT, ORDER BY, basic WHERE conditions, and basic operators.

In my second post, we’ll move into pulling data from related standard and custom objects using dot notation and subqueries.

In my third post, we’ll divedeeper intoaggregates, advanced operators and wildcards, working with dates and numbers, and more advanced WHERE conditions.

This is not an exhaustive (nor hopefully, exhausting) guide to SOQL. It’s just the bits and bobs that I use most often, and that should be enough to get you started.

What is SOQL?

SOQL (pronounced Sock-le) is the language that we can use to programmatically pull data sets from Salesforce standard and custom object (also known as “query the database”).. It’s based on the very popular SQL (Structured Query Language), with a few notable exceptions. We’ll talk about them in Part Two, but for now I’m going to assume you know nothing about SQL (and if you do…bonus!).

Let’s dive into some code samples!

SOQL’s basic structure

There are two required elements to any SOQL statement:

SELECT – These are the field(s) you want to retrieve data from within your Salesforce database

FROM – This is the Salesforce object you are wanting to pull the fields from

For example, if I wanted to pull out (Select) all the names and email addresses from the Contact object, I would use the following SOQL statement:

SELECT name, email FROM contact

Easy right?!

A few things to note here that might not be obvious on first glance:

All objects and fields are referenced by their Salesforce API name (as opposed to their labels). You can find these out under the Salesforce setup for the custom or standard object or field. Note that custom object and custom field API names end with “__c” (underscore underscore c), just like when you use them in formulas.

You will separate each of the fields that you want to pull data from with a comma (e.g., SELECT name, email, account).

With very few exceptions (which I’ll make sure to point out) SOQL is not case sensitive, but I am using one of the common SQL/SOQL naming conventions that makes things easier to read. I’ve made all the keywords (e.g., SELECT, FROM) uppercase and the rest camelCase.

Now that we’ve established that SELECT and FROM are the only required keywords in a SOQL statement (also known as a “Select Statement”), let’s look at some of the optional, but very useful, other SOQL keywords.

The LIMIT keyword

Because Salesforce is a multi-tenant environment, it would be bad form to run the query above, because you may have many thousands of records, and it could take a lot of processing power to run.

Although it isn’t required, you often want to use the LIMIT keyword (especially if you’re just learning SOQL). This is handy because it…well…limits how many records come back.

SELECT name, email FROM contact
LIMIT 3

You guessed it, this would pull up only three records. But which three? This doesn’t seem very useful, does it? Wouldn’t it be nice to control which ones were pulled, and the order in which they are displayed? Hell yes! That’s where the ORDER keyword comes into play.

The ORDER BY keywords

This is a handy-dandy little section (or “clause”, for those in the know) of SOQL because it specifies exactly how you want your resulting record set to be sorted. If you’re using LIMIT, it will also control which records are pulled…the first three in your ordered list.

SELECT name, email FROM contact
ORDER BY email, name
LIMIT 3

Pretty easy really, just indicate the way you want things to be ordered and voila! If you don’t indicate an order, then by default, SOQL will assume you want ascending (ASC) order, but you can also specify descending (DESC) order:

SELECT name, email FROM contact
ORDER BY name DESC, email
LIMIT 3

The code above first sorts by name descending (Z-1), and then within records with the same name, it will then sort by email in ascending (1-Z) order. Each field in the ORDER BY clause has its own sort order, therefore because we didn’t specify any particular order for the email field, it is by default giving an ascending order.

So again, this is fun, but it’s still not all that helpful. There are some use cases for this (like finding the top 5 accounts by their total opportunity value), but generally you are wanting to filter for a smaller subset of specific records.

Do I have the keyword for you!

The “WHERE” keyword

More about WHERE

Note that in Part One of this series, we’re only dealing with text fields within WHERE clauses, and only using the “equals” operator.

In Part Two of this series we will introduce how to filter by numeric, multi-select pick lists, date and other types of fields, and working with text and wildcards (and what the heck a wildcard is!).

Let’s look at a more common scenario: What if we only wanted to select the names and email addresses from the contact object, wherethe first name of the person equals “Jane” and the last name equals “Smith”?

For that we need to use another keyword to refine/filter our database query; the oh-so-awesome WHERE keyword.

When filtering for data in text fields, surround your filtered value with single quotes (e.g., ‘Jane’)

SOQL WHERE conditions that include text fields and use “=” operator are not case sensitive, which means that the query above would find all of the following names: “Jane Smith,” “jane smith,” “Jane smith,” “jane Smith,” and even “JaNe SmItH”!

Using multiple fields in a WHERE statement

It is very common to want to filter by more than one field in your SOQL statements. There are a few ways to do this, depending on what you’re filtering for (and by).

The example above uses the AND keyword between the two fields we wanted to filter by (e.g., first and last name).

In effect, we’re asking SOQL to find all contacts where both of the following are true:

firstName equals ‘Jane’

lastName equals ‘Smith’

Looking for different values within the same field

But wait…there’s more!

There is actually a third way to do this, using wildcards, but we’re going to wait to deal with that in Part Two of this Sweet SOQL Series

What if we weren’t sure how Jane spelled her last name and wanted to find both “Smith” and “Smyth”? We can do this using the OR keyword. Take a look at the following:

WHERE lastName = 'Smith' OR lastName = 'Smyth'

Another way to look for multiple values within the same field is using the IN keyword:

WHERE lastName IN ('Smith','Smyth')

The IN keyword allows us to define a list of values by placing them inside parentheses and separated with commas.

The two WHERE statements above would produce the same result set, but you can probably see the advantages in this way of doing things. In the second example, we don’t have to restate the field by which we’re filtering (lastName), which saves time and space in our code.

Imagine that we were looking for all the contacts that were in Western states; using IN is going to save you a LOT of typing, and have the added bonus of it being easier to read:

WHERE state IN ('CA','WA','OR','NV','ID')

Versus

WHERE state = 'CA' OR state = 'WA' OR state = 'OR' OR state = 'NV' OR state = 'ID'

There is another great use case for IN, which is to solve another sticky SOQL issue with something called a “Sub Query.” We’ll talk about that later. <cue anticipatory music>

But back to our original data question; we don’t want to find just any old Smith (or Smyth), but only those with the first name of “Jane”; how do we do that? Pretty simple really:

WHERE firstName = 'Jane' AND lastName IN ('Smith','Smyth')

Where can I play around with SOQL?

You might be wondering, “This is cool an’ all, but where can I play around with this stuff?” There are two handy dandy tools that I use when working with SOQL, one is the handy dandy Developer Console, and the other is Workbench. With both of these tools, you can create and run queries. Workbench is great because it gives you access to a whole bunch of tools beyond SOQL development. The Developer Console is great too; I’ve included instructions below on how to use the Developer Console as it’s a great way to get comfy with all these concepts.

To access your Developer Console:

Log into your developer org (or your sandbox)

Click on your name at the top right of the screen

Choose “Developer Console.”

Once you’re there, click into the Query Editor tab (in the bottom section) and you’ll see and area to type in your SOQL query.