Salesforce SOQL tutorial
What is SOQL?
SOQL (Salesforce Object Query Language) is the Salesforce Platform’s query language, primarily used to fetch Salesforce records from Salesforce objects. In Salesforce data resides in standard objects (such as Account, Contact, Opportunity) and custom objects inside your Salesforce organization.
Salesforce is built on top of a multi-tenant architecture where many users are sharing the same database. SOQL provides a medium by which Salesforce can prevent queries from adversely affecting customers who rely on shared resources.
SOQL is mainly used in APEX (Salesforce object-oriented programming language). Apex uses SOQL queries to fetch the required data stored in Salesforce records. These records can then be directly shown in VF pages, lightning components or they can be processed programmatically depending on the business logic.
SOQL is also the underlying mechanism used by Salesforce Reports and List Views to show a set of records.
SOQL VS SQL
SOQL is functionally similar to SQL(Structured Query Language), which is a programming language designed for managing data in a relational database.
SQL can be used to insert, update and delete data from database tables. Not only that, but it can also be used to create new database tables.
SOQL (Unlike SQL) with we can only fetch data from Salesforce objects (which correspond to database tables, in SQL world). Also, not all the features of the SQL
SELECT statement are supported. Advanced features such as arbitrary join operations, or wildcards in field lists (Select * from …), or calculation expressions are not supported in SOQL.
Main differences between SQL and SOQL
- You can only perform queries using the
SELECT *is not supported in SOQL. You have to explicitly select the desired fields.
DELETEstatements are not supported in SOQL.
- SOQL does not support the
- In SOQL, you can only alias fields in aggregate queries that use the
Why SOQL do not support
The main reason is that Salesforce is a multi-tenant environment where millions of users share the same database, and you don’t want the database server to get in too much trouble performing very complicated tasks, and thus lagging performance for other tenants of the server. Due to the same multi-tenant architecture, various limits are placed on the execution of SOQL queries which we see in a later section.
- Salesforce uses Oracle databases to store data.
- In each POD, all Salesforce customer data are stored in one single table called MT_Data.
- The table MT_Data contains 500 columns (Value0 ... Value500) in addition to technical columns such as GUID, OrgID, ObjID ... . This is why we can not create more than 500 Custom Fields for each object.
For more information about Salesforce Multitenant Architecture
The General SOQL Syntax
A typical SOQL query had the following structure:
This query will be executed in the following ORDER:
- Find records that satisfy the constraint
- Sort the records by the specified data in either ascending or descending order and
- Return the
Keep in mind that
ORDER BY and
LIMIT clauses are optional.
Following a SOQL query to get a maximum of 5 accounts in country France and order results based on Name field
How can we execute SOQL queries?
Usually, you will need to test SOQL queries before adding them to your APEX code. Also, you might just need to look at Salesforce Data to find specific records. In those cases, you can use the Developer Console or Workbench.
Execute SOQL queries using The Developer Console
The Developer Console is an integrated development environment by Salesforce. The Developer Console can be used to run anonymous Apex Code, create Apex Classes, debug Apex code, create Lightning Components, create Visualforce Pages, run SOQL queries, and much more.
Steps to execute a SOQL query using The Developer Console :
- Login to Salesforce using your credentials.
- Open Developer Console from drop-down against your name.
- Select the Query Editor tab from console.
- Enter your query and click on execute to view results. for example:
Fig: How to Open Developer Console in Salesforce Classic
Fig : How to execute query in Developer Console
Execute SOQL queries using Workbench
Workbench is a highly popular 3rd party tool that runs on top of Salesforce. It can be used to run anonymous apex, test Salesforce REST and SOAP API, or to execute SOQL queries against the Salesforce database on the fly.
Steps to execute a SOQL query using Workbench:
- Go to https://workbench.developerforce.com/login.php
- Login to your production/ sandbox org using your credentials.
- When you login for the first time the app will prompt to give permissions to execute data in Salesforce, click allow.
- After workbench opens, go to “queries → SOQL query” to open the query editor.
- Enter your query and click on “query” to view the results.
Fig : open SOQL query using workbench
Fig: Execute a SOQL query in workbench