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 dive deeper into aggregates, 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

Let’s look at a more common scenario: What if we only wanted to select the names and email addresses from the contact object, where the 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.

SELECT name, email
FROM contact
WHERE firstName = 'Jane' AND lastName = 'Smith'

A couple things to note here:

• 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

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:

1. Log into your developer org (or your sandbox)
2. Click on your name at the top right of the screen
3. 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.

To execute a query:

1. Enter your SOQL statement into the box immediately below the Query Editor Tab
2. Click the Execute button

The Query Results will appear in the window above, with the number of rows your query resulted in.

You can find more information on the Salesforce Developer Console Query Editor on the following Salesforce Help page.

References

• Force.com SOQL and SOSL Reference (Salesforce)
• SOQL: A Beginner’s Guide (sfdc99.com)
• SOQL – How I Query With Thee, Let Me Count the Ways (blog.jeffdouglas.com)
• Learning Salesforce.com Workbench Part 1 (ButtonClickAdmin.com)
• Using the Developer Console (Salesforce)
• Writing SOQL Queries (Salesforce Trailhead Unit)

Wrapping Up

Phew! I hope that you made it through this intro and are starting to get into the SOQL swing of things. I’m having a blast introducing you to SOQL. Let’s summarize what we know so far…

• SOQL is used to pull data out of Salesforce Objects (also known as “query the database”).
• Like Apex, for the most part, SOQL is not case sensitive.
• There are only two required keywords for every SOQL statement:
  • SELECT – Which fields do we want to pull data from?
  • FROM – What object are you pulling from?
• There are a number of optional keywords, including:
  • LIMIT – How many records do you want to pull?
  • ORDER BY – What sort order do you want? ASCending or DESCending?
  • WHERE – How do you want to filter the results?
• Within WHERE clauses, we can use a number of operators, including:
  • Equals (“=”)
  • IN – Used to filter by a comma separated list of values
  • AND – Used to combine multiple filters; both sides of the AND must be true
  • OR – Used to combine multiple filters; only one side of the OR needs to be true

We’re officially at the end of Part One of our intro to SOQL, and I hope that it was satisfying; not too salty, not too sweet, and left you wanting more!

Don’t worry! Next week I’ll be giving you Part Two of “A Sweet Intro to SOQL,” where I’ll tempt you with some more sassy SOQL concepts, like how to pull data from related standard and custom objects.

Until next time…
See you in the cloud coding kitchen!