What is SOQL ? : SOQL Queries | A Definitive Guide [ OverView ]

Last updated on 25th Dec 2021, Blog, General

(5.0) | 19389 Ratings 1626

Share:

SOQL stands for Salesforce Object Query Language. You can use SOQL to read information stored in your org’s database. SOQL is syntactically similar to SQL (Structured Query Language).

• Salesforce Object Query Language (SOQL)
• When to Use SOQL
• What are the SOQL features?
• SOQL Syntax and Explanation
• How to Write SOQL Statements
• SOQL (Salesforce Object Query Language)
• Where clause in SOQL
• Deciding Which to Use
• Performance Considerations
• Conclusion

Salesforce Object Query Language (SOQL):

Use Salesforce Object Query Language (SOQL) to search your organization’s Salesforce data for specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL), but is designed specifically for Salesforce data.

• With SOQL, you can build simple yet powerful query strings in multiple environments.
• Using the queryString parameter of the SOAP API query() call. See query() in the SOAP API Developer Guide.
• Using the q parameter of a REST API query request. See Query in the REST API Developer Guide.
• Using Apex Statements. See SOQL and SOSL Queries in the Apex Developers Guide.
• Using Visualforce controllers and getter methods. See Controller Methods in the Visualforce Developer Guide.
• Using Salesforce CLI. force view:data:sql:query in data commands subject of Salesforce CLI command context.
• Using Extensions for Visual Studio Code. See Write SOQL Query in Salesforce Extensions for Visual Studio Code.
• Similar to the SELECT command in Structured Query Language (SQL), SOQL allows you to specify a source object (such as an account), a list of fields to retrieve, and the conditions for selecting rows in the source object.
• SOQL does not support all the advanced features of the SQL SELECT command. For example, you cannot use SOQL to perform arbitrary join operations, use wildcards in field lists, or use enumeration expressions.



SOQL uses a SELECT statement combined with a filtering statement to return a set of data, which can optionally be ordered:

• Select one or more fields
• from something
• where the filter statement and, optionally, the results are ordered



For example, if the value of Name is Sandy, the following SOQL query returns the values ​​of the ID and Name fields for all Account records:

• select id, name
• from account
• where name = ‘sandy’
• Apex requires that you surround SOQL and SOSL statements with square brackets to use them in your statements. You can use Apex Script variables and expressions when preceded by a colon (:).
• For a complete description of the syntax, see SOQL SELECT Syntax.

When to Use SOQL:

Use SOQL when you know what objects the data resides in, and you want to:

• Retrieve data from a single object or from multiple objects related to each other.
• Counts the number of records that meet the specified criteria.
• Sort the results as part of the query.
• Retrieve data from number, date or checkbox fields.
• With stored data and large objects, you can only use some SOQL features. For more information, see SOQL with Large Objects.

What are the SOQL features?

• SOQL is case insensitive.
• SOQL is much easier to understand when compared with SQL.
• SOQL helps in optimizing the code.
• SOQL reduces our workload and time.
• Data from objects can be extracted to excel sheets using SOQL.
• SOQL questions will be required in Apex programming.
• Extensive knowledge in SOQL will help in becoming a good developer.



SOQL Purpose.

• Salesforce Object Query Language in Apex Statements.
• In Visualforce controller and getter methods.
• In the schema explorer of the Force.com IDE.
• Salesforce Object Query Language (SOQL) is case insensitive which means we can write in uppercase, lowercase and both lower and uppercase. For our convenience in our Salesforce Developer tutorial, we use uppercase for SOQL keywords and lowercase letters for names, field lists, and conditions.
• When SOQL is used we can optimize the code which results in faster execution. With the help of Salesforce Object Query Language (SOQL), data can be extracted from Salesforce custom objects and standard objects.
• In SOQL we use API names for standard and custom objects. Normally custom object API names always end with __c.

SOQL Syntax and Explanation:

SOQL Syntax: In our previous Salesforce tutorial, we have learned about what SOQL is. In this Salesforce Training Tutorial, we will go through the examples and explanation of SOQL Syntax.

SOQL Syntax

The Salesforce Object Query Language uses the SELECT keyword to fetch data from objects and fields. In Structure Query Language (SQL), the keyword SELECT is used to fetch data.



• select field
• from the object
• where position
• Limit to order for viewing or reference
• offset
• update viewstate



From the above syntax we need to know clearly about each word in the syntax. Remember the following things carefully.

Fields: It uses the API names of an object when we mention fields.

Commodity: When we refer to a commodity it refers to custom and standard items.

condition: We use conditions in SOQL to filter the records.

Ordering: It is used to order the results. It can be optional in SOQL.

Limit: This limits the number of records while fetching.

To View: It is used to get the records from the records received on the last day.For reference: It is used to update LastReferencedDate.

OFFSET: Used to indicate the starting line of the record.

Update View Status : It updates the graph view statistics for the retrieved records.When writing SOQL statements we use API names for fields and labels of fields should not be used.

Standard Objects: The field name is referred to as the API name.Custom Object: The API Name column refers to the API name.

How to Write SOQL Statements:

• How to Write a SOQL Statement: In our previous Salesforce tutorial, we learned how to download and install the Force.com Explorer software to write your first SOQL statement. In this Salesforce training tutorial, we will learn how to write our first SOQL statement.


• So in order to execute our SOQL queries we need to install some tools which execute SOQL statements in Salesforce.com. In our previous Salesforce training article we learned how to download and install the Force.com Explorer software. In our next SOQL Salesforce tutorial we use only the Force.com Explorer software.


• How to write a SOQL statement?- In this example we are taking a standard object named “Account”. Account is a standard object where we store information about our customers and partners.


• Select ID, Name from Account- From the above SOQL statement we are getting the id and name of a standard object named “Account”. How to write SOQL statement.


• These are some examples of SOQL statement questions. In this Salesforce tutorial, we have learned basic SOQL statements. We have seen getting records from standard object and custom object.

SOQL (Salesforce Object Query Language):

SOQL (Salesforce Object Query Language) retrieves records from a database using the “SELECT” keyword.

Using SOQL, we can know in which object or field the data resides.

We can get data from a single object or from multiple objects related to each other.

SOQL uses a SELECT statement combined with a filtering statement to return a set of data, which can optionally be ordered:



• Select one or more fields
• from something
• where the filter statement and, optionally, the results are ordered
• SOQL query is enclosed in square brackets. The following query retrieves a record from the database whose name field is equal to the value “airway”.
• Account a = [SELECT id, name FROM account WHERE name = ‘acc1′];
• In the above query the “a” variable stores the id, the name of all the accounts with the name “acc1”
• SOQL statements evaluate to a list of SObject records, single SObject records, or an integer for the count method queried.
• Querying Single SObject Records: The query below gets a single Account record with the given ID.
• Account A = [SELECT NAME FROM ACCOUNT WHERE ID=’XXXXXXXXXXXX’];
• Query List of SObjects Records: The following example is to display the list of Accounts.
• // Query list of records and store in list variable “acc”
• List<'Account> acc = [SELECT NAME FROM ACCOUNT WHERE INDUSTRY = ‘Education’];
• System.debug(‘Size:’ + acc.size()); // Displays the size of the list
• for (integer i=0; i<=acc.size();i++)
• ,
• System.debug(‘size:’ + acc[i].name); // to display the accounts stored in “acc”
• Below query gets the number of records in an object.
• int i = [select count() from account where industry = ‘education’];
• System.debug(‘Count: ‘ + i);

Where clause in SOQL:

The WHERE clause is also called a conditional expression. The WHERE clause is used to filter the retrieved data.

Whenever we want to filter data from a set of object records we use the WHERE clause in SOQL. This WHERE clause filters the data based on the given condition or criteria. Basically when a WHERE clause is used in a SOQL statement, the WHERE clause is followed by some comparison operators and logical operators like IN operator, NOT IN operator, INCLUDES operator, EXCLUDES operator.



• Let’s look at an example on the WHERE clause.
• SELECT FIRST NAME, LAST NAME FROM CONTACT WHERE FIRST NAME != NULL
• From the above SOQL statement we are getting data from a standard object named “Contact”. We are using the conditional expression “WHERE” to filter the data retrieved from the Contact object. Preceding where we have used != operator to filter data first name which is not null.
• Let us look at another example to understand the WHERE clause in SOQL.
• SELECT FIRST NAME, LAST NAME FROM CONTACT WHERE LAST NAME = ‘SELECT
• Where Clause in SOQL
• With SOQL statements we are fetching data from Contact whose first name is “Adarsh”. In SOQL statements text words are mentioned with ”.

Develop Your Skills with Salesforce Platform Developer Certification Training

Weekday / Weekend BatchesSee Batch Details

Deciding Which to Use:

A SOQL query is equivalent to a SELECT SQL statement and searches the org database. SOSL Search is a programmatic way of performing text-based searches against indexes.

Whether you use SOQL or SOSL depends on whether you know what objects or fields you want to search, as well as other considerations.

Use SOQL when you know what objects the data resides in, and you want to:



• Retrieve data from a single object or from multiple objects related to each other.
• Counts the number of records that meet the specified criteria.
• Sort the results as part of the query.
• Retrieve data from number, date or checkbox fields.
• Use SOSL when you don’t know what object or field the data is in, and you want to:
• Retrieve data for a specific word that you know is present in a field. Since SOSL can tokenize multiple words within a field and build a search index from it, SOSL searches are faster and can return more relevant results.
• Efficiently retrieve multiple objects and fields where objects may or may not be related to each other.
• Retrieve data for a particular division in an organization using the Division feature.
• Recover data in Chinese, Japanese, Korean or Thai. Morphological tokenization for CJKT terms helps ensure accurate results.

Performance Considerations:

To increase the efficiency of queries and searches, keep in mind:

• Both the SOQL WHERE filter and the SOSL search query can specify the text you should be looking for. When a given search can use any language, SOSL is generally faster than SOQL if the search expression uses the word CONTAINS.


• SOSL can tokenize multiple words within a field (for example, multiple words separated by spaces) and build a search index from it. If you’re searching for a specific specific term that you know exists in a field, you may find that SOSL is faster than SOQL for these searches. For example, if you were searching for “John” against a field with a value such as “Paul and John Company”, you could use SOSL.


• Keep the number of fields to be searched or asked to a minimum. Using multiple fields leads to multiple permutations, which can be difficult to tune.


• Send queries and searches- Build and execute queries and searches using one of several environments, including SOAP API, REST API, Apex, and more. For more information about which environments support queries and searches, see Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL).

Conclusion:

I will assume for a moment that you are familiar with basic SQL for the purposes of this tutorial. If you’re not, there are plenty of great resources on the web for brushing up. As a developer who wants to expand Salesforce.com, SOQL is a very important and powerful aspect of coding. You can use SOQL to create your own custom query strings. These query strings can be used in the following places:




Top statement

• Visualforce getter methods and controllers
• String param in query passed in query() call
• Finally, you can use the Schema Explorer in the Eclipse Toolkit (it’s very clever)
• For those of you familiar with SQL, you’ll find some differences but for the most part SOQL does everything you’ll need.
• The LIKE operator provides a way to match partial text strings and includes support for wildcards. Let’s say for a moment that we want to find all leads where the email domain is the same. For this we can use the “LIKE” operator. That’s an example of a LIKE statement with the % wildcard.
• The location of the percent sign ‘%’ is important here. I’m basically saying, bring me back all leads where the email ends with “somecompany.com”. That’s why I put ‘%’ at the beginning of whatever I’m looking for.” Anything to the left of the % sign is ignored in search.