REGISTER NOW: Supercharge Your Cloud Integrations and Register for our Webinar on 9/26

Kloudless Blog

Tutorials, case studies and how-tos from our experts

Monday Motivation: Salesforce SOQL Vs SOSL

Photo by Samuel Zeller on Unsplash

“If we always helped one another, no one would need luck.”

It’s Monday, and we’re back with another helpful post centered on helping you overcome some of the most commonly asked questions about the APIs we work with here at Kloudless. Every Monday, we will aim to help answer some of the oft-asked inquiries into the world’s most popular APIs. Today, we will be focusing on the industry’s leading solution for all things CRM: Salesforce.

When it comes to querying Salesforce for pertinent data, there are two language solutions in place to find the Salesforce objects you need from your database: SOQL (Salesforce Object Query Language) and SOSL (Salesforce Object Search Language).

So, what’s the difference between these two languages and what are the similarities between them? Let’s take a minute to break down what features are offered by each respective language, and the proper place for their usage.

Language Breakdown

SOQL

Salesforce Object Query Language (SOQL) is very similar in syntax and usage to that of SQL. SOQL lets you specify the source object (such as Account), a list of associated fields to retrieve, and conditions for selecting rows in the source object. SOQL uses the SELECT statement combined with filtering statements to return sets of data, which may optionally be ordered. 

SOQL can be indexed by a number of different columns for faster retrieval including Primary keys, Foreign keys, audit dates, and even custom fields as well. Feilds that cannot be indexed are picklists, currency fields, long text fields, and binary fields.

Syntax-wise, as stated above, SOQL is very similar to SQL. The following query will return the name and ID from all accounts where the name is “Kloudless”:

So, when exactly is the best time to use SOQL?

Based on the recommendations of Salesforce themselves, SOQL is best used when you know what object or fields the data you’re looking for resides in. SOQL also tends to be best for retrieving data where there is some sort of relations in place, either within a single object or across multiple objects. SOQL, like SQL, is best for retrieving a count of the number of records

that meet specific criteria. Finally, SOQL is the only official Salesforce language capable of retrieving data from number, date, or checkbox fields. 

SOSL

Unlike SOQL, which can only query one object at a time, SOSL enables you to search text, email, and phone fields for multiple objects simultaneously. SOSL also uses a unique syntax to perform its queries. SOSL also provides the ability to query against encrypted fields, which proves incredibly useful depending on the search.

SOSL does not have robust documentation within Salesforce to explain its indexing, although specified columns can be indexed, such as name, ID, or even unique fields.

SOSL syntax, as stated above, is rather unique. The following will return the name and phone number for all records that match the name “David Hallinan”:

So, when exactly is the best time to use SOSL?

Salesforce recommends using SOSL when you do not know the object or field that the data resides in, and would like to find it in the most efficient way possible. SOSL is also the most efficient way to retrieve non-relational data of multiple objects and fields. A unique feature of SOSL is also its ability to query divisions within Salesforce objects. 

So What’s Best?

There really isn’t an answer to this question, as both languages have their strengths and weaknesses depending on the data being queried. SOQL is considerably slower than SOSL, so if a large amount of data is being queried, SOSL may prove to be the better choice. SOQL, on the other hand, tends to be more of a precise method of returning data. It can be used to create queries that are search-like, however, it is far more suitable for precision like that of its cousin SQL. SOSL can be far easier to query data where the associated SOQL statement would be comprised of chaining many ‘WHERE’ statements together. Finally, if new fields are added to the system, SOQL will not be able to pick up on those changes, and SOSL must be used. Remember, searches comprised of both languages are possible, so when in doubt, use both of them.

If your application is providing CRM integrations, why offer a single service when you can provide your users with any of their preferred services? In the same amount of code it would take to connect to a single service, you can provide your users with up to dozens of competing CRM providers through the Kloudless Unified CRM API. Click here to learn more!

Published By

David Hallinan

David Hallinan is Head of Content at Kloudless. He enjoys painting, JavaScript, vintage synths, drum machines and forcing his sports allegiances on his children.

View all posts by David Hallinan