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.

Important
SOQL can only be used to fetch data. For other data manipulation operations, Salesforce uses DML(data manipulation language) inside Apex.

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 statement.
  • SELECT * is not supported in SOQL. You have to explicitly select the desired fields.
  • INSERT, UPDATE, and DELETE statements are not supported in SOQL.
  • SOQL does not support the JOIN keyword.
  • In SOQL, you can only alias fields in aggregate queries that use the GROUP BY clause.

Why SOQL do not support SELECT *

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.

Under the cover
  • 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:

SELECT [fields]
FROM [object]
[WHERE filter_expressions]
[ORDER BY list_of_field_names ASC/DESC]
[LIMIT n]

This query will be executed in the following ORDER:

  1. Find records that satisfy the constraint filter_expressions
  2. Sort the records by the specified data in either ascending or descending order and
  3. Return the n first records

Keep in mind that WHERE, 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

SELECT Id, Name, BillingCountry
FROM Account
WHERE BillingCountry = 'France'
ORDER BY Name ASC
LIMIT 5
SOQL case sensitivity
The SOQL is case-insensitive, but SOQL Keywords (SELECT, FROM, WHERE, AS, ORDER BY, HAVING, GROUP BY, etc) are usually written in all capitals.

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 :

  1. Login to Salesforce using your credentials.
  2. Open Developer Console from drop-down against your name.
  3. Select the Query Editor tab from console.
  4. Enter your query and click on execute to view results. for example:
SELECT Id, Name, BillingCountry
FROM Account
WHERE BillingCountry = 'France'
ORDER BY Name ASC
LIMIT 5

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:

  1. Go to https://workbench.developerforce.com/login.php
  2. Login to your production/ sandbox org using your credentials.
  3. When you login for the first time the app will prompt to give permissions to execute data in Salesforce, click allow.
  4. After workbench opens, go to “queries → SOQL query” to open the query editor.
  5. Enter your query and click on “query” to view the results.

Fig : open SOQL query using workbench

Fig: Execute a SOQL query in workbench