Swapnil SFDC | Salesforce Blog

What is Salesforce Object Query Language (SOQL) in APEX ?

SOQL means Salesforce Object Query Language which is to query the records from the database.com based on the requirement.

Apex has direct access to Salesforce records that are stored in the database, you can embed SOQL queries in your Apex code and get results in a straightforward fashion. When SOQL is embedded in Apex, it is referred to as inline SOQL.

To include SOQL queries within your Apex code, wrap the SOQL statement within square brackets and assign the return value to an array of sObjects.
For example, the following retrieves all account records with two fields, Name and Phone, and returns an array of Account sObjects.

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

Retrieve data from a single object or from multiple objects that are related to one another.
Count the number of records that meet specified criteria.
Sort results as part of the query.
Retrieve data from number, date, or checkbox fields.

Performance Considerations

To increase the efficiency of queries and searches, keep in mind:
Both SOQL WHERE filters and SOSL search queries can specify text you should look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses a CONTAINS term.
SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.
Keep the number of fields to be searched or queried to a minimum. Using many fields leads to many permutations, which can be difficult to tune.

The following are SOQL keywords that can’t be used as alias names:
AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, USING, WHERE, WITH

When is SOQL Query used?

SOQL queries is used to retrieve data from single object or from multiple objects.
SOQL is used to count the number of records that meets the evaluation criteria.
It is used to sort results.
It is used to retrieve data from number, data and checkbox fields.
Salesforce Object query language (SOQL) is used in the queryString parameter in the query ( ) call.
In Apex statement.
In visualforce controllers and getter methods.
In the schema explorer of the force.com IDE.

Prerequisites:

Some queries in this unit expect the org to have accounts and contacts. Before you run the queries, create some sample data.

Use the Query Editor:

The Developer Console provides the Query Editor console, which enables you to run your SOQL queries and view results. The Query Editor provides a quick way to inspect the database. It is a good way to test your SOQL queries before adding them to your Apex code. When you use the Query Editor, you must supply only the SOQL statement without the Apex code that surrounds it.

How to Run / Execute SOQL Query:

Go to Salesforce org
Click on Gear Icon (Dropdown menu)
Click on Developer Console
In the Developer Console, click the Query Editor tab.
Copy and paste the following any SOQL Code line into the first box under Query Editor, and then click Execute.

Note:
Only API Names are supported in SOQL and does not the lables.

# Get All account records in your org:


SELECT Name,Phone FROM Account

Basic SOQL Syntax:

This is the syntax of a basic SOQL query:


SELECT fields FROM ObjectName [WHERE Condition]

The WHERE clause is optional.

"WHERE" is the keyword to be used in-order to filter records
Condition Expression (Criteria) has to be specified after the WHERE keyword and only the record that satisfy the criteria will be retrieved

Let’s start with a very simple query.
For example, the following query retrieves accounts and gets Name and Phone fields for each account.


SELECT Name,Phone FROM Account

The query has two parts:

SELECT :
Fields to be fetched.
This part lists the fields that you would like to retrieve. The fields are specified after the SELECT keyword in a comma-delimited list. Or you can specify only one field, in which case no comma is necessary (e.g. SELECT Phone).
FROM :
Object from which the fields are to be fetched.
This part specifies the standard or custom object that you want to retrieve. In this example, it’s Account.

What Are The Types of SOQL Statements in SalesForce?

There are two types of SOQL Statements:

Static SOQL
Dynamic SOQL

1. Static SOQL:
Static SOQL is written in [ ] array brackets. Its good to use when you did not have dynamic changes is SOQL.


Account[] acc = [SELECT Name, Phone FROM Account];

2. Dynamic SOQL:
Dynamic SOQL refers to the creation of a SOQL string at run time with Apex code. Dynamic SOQL enables you to create more flexible applications. For example, you can create a search based on input from an end user or update records with varying field names.


String myTestString = 'TestName';
List<sObject> sobjList = Database.query('SELECT Id FROM Account WHERE Name = :myTestString');

Correct order of SOQL execution:
SELECT <FIELONAMES> FROM OBJECTNAMIE
[WHERE <CONDITION>]     //OPTIONAL
[GROUP BY <FIELDNAME>]     //OPTIONAL
[HAVING <CONDITION>]     // OPTIONAL
[LIMIT <NUMBER>]      // OPTIONAL
[OFFSET <NUMBER>]     //OPTIONAL
[ALLROWS]     //OPTIONAL

The following are examples of text searches that use SOQL:

# Simple query (Fetch Name and Phone from Account object):


SELECT Name,Phone FROM Account

CustomObject:


SELECT Id, Name, Student_Name__c, Courses__c, DOB__c, Email__c, Roll_No__c FROM  Student__c

# WHERE : (To apply filter before grouping)


SELECT Name FROM Account WHERE Name = 'John'


SELECT Id, Name, Amount FROM Opportunity WHERE StageName = 'Closed Won'


SELECT Name, (SELECT LastName FROM Contacts) FROM Account WHERE Name = 'SFDC Computing'


SELECT Account.Name FROM Contact WHERE FirstName = 'Carol' AND LastName='Ruiz'


SELECT Name,Phone FROM Account WHERE (Name='SFDC Computing' AND NumberOfEmployees>25)


SELECT Name,Phone FROM Account WHERE (Name='SFDC Computing' AND NumberOfEmployees>25) ORDER BY Name LIMIT 10

# NULL :

(Null values in WHERE clauses)


SELECT Id, Name, Phone FROM Account WHERE Phone = null


SELECT Id FROM Case WHERE Contact.LastName = null


SELECT Id, Name, Phone, Email FROM Contact WHERE Email = NULL AND Phone = NULL

# NOT NULL :
(Not Null != values in WHERE clauses)


SELECT Id, Name, Phone FROM Account WHERE Phone != null


SELECT AccountId FROM Event WHERE ActivityDate != null


SELECT Id FROM Case WHERE Contact.LastName != null


SELECT Id, Name, Phone, Email FROM Contact WHERE Email != NULL AND Phone != NULL


SELECT Id, Name, Phone, Email FROM Contact WHERE Email = NULL AND Phone != NULL


SELECT Id, Name, Phone, Email FROM Contact WHERE Email != NULL OR Phone != NULL


SELECT Id, Name, Phone, Email FROM Contact WHERE (Phone != NULL OR MobilePhone != NULL) AND Email != NULL

# ORDER BY with DESC:
"ORDER BY" is the keyword to be used to sort records.

ASC or DESC : Specifies whether the results are ordered in ascending (ASC) or descending (DESC) order. Default order is ascending.
NULLS FIRST or NULLS LAST : Orders null records at the beginning (NULLS FIRST) or end (NULLS LAST) of the results. By default, null values are sorted first.


SELECT Name FROM Account ORDER BY Name DESC


SELECT Name FROM Account ORDER BY Name DESC NULLS LAST

# ORDER BY with ASC:


SELECT Name FROM Account ORDER BY Name ASC


SELECT Name FROM Account WHERE industry = 'media' ORDER BY BillingPostalCode ASC NULLS LAST LIMIT 25

# LIMIT:


SELECT Name,Phone FROM Account LIMIT 1


SELECT Name FROM Account WHERE Industry = 'media' LIMIT 30

# ORDER BY with LIMIT :


SELECT Name FROM Account WHERE Industry = 'media' ORDER BY BillingPostalCode ASC LIMIT 110

Aggregates Functions:
# AVG() :
(Get Opportunity: Amount Average)


SELECT AVG(Amount) FROM Opportunity


SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId

# SUM() :
(Returns the total sum of a numeric field, you can not add another field in query)


SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60


SELECT SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won'

# MIN() :
(Returns the minimum value of a field.)


SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName

# MAX() :
(Returns the maximum value of a field.)


SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name

# Count() :
(Get contact total count, you can not add another field in query)


SELECT COUNT() FROM Contact

# COUNT(fieldName) :
(Get contact ID Field total count)


SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'

# COUNT(fieldName) :
(Get contact ID and CampaignId Field total count) (Multiple)


SELECT COUNT(Id), COUNT(CampaignId) FROM Opportunity

# COUNT_DISTINCT() :
(Returns the number of distinct non-null field values matching the query criteria)


SELECT COUNT_DISTINCT(Company) FROM Lead

# GROUP BY :


SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

# MIN, MAX, SUM with GROUP BY :


SELECT Name, MAX(Amount), MIN(Amount) min, SUM(Amount) FROM Opportunity GROUP BY Name

# GROUP BY One ROLLUP :
(This simple example rolls the results up by one field)


SELECT LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(LeadSource)

# GROUP BY Two ROLLUP :
(This example rolls the results up by two fields)


SELECT Status, LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(Status, LeadSource)

# HAVING (To apply filter after Grouping):


SELECT Name, COUNT(Id) FROM Account GROUP BY Name HAVING COUNT(Id) > 1


SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource HAVING COUNT(Name) > 100 and LeadSource > 'Phone'

# OFFSET :
The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.
"OFFSET" Is the keyword to be used to specify the index of starting row while fetching records.
(eg. get record from particular record onwards from 100 ==> 101,102.103....)


SELECT Name, Id FROM Account OFFSET 100

# OFFSET with ORDER BY :


SELECT Name, Id FROM Account ORDER BY Name OFFSET 100

# OFFSET with ORDER BY and LIMIT :


SELECT Name, Id FROM Account ORDER BY Name LIMIT 20 OFFSET 100


SELECT Name, Id, ( SELECT Name FROM Opportunities LIMIT 10 OFFSET 2 ) FROM Account ORDER BY Name LIMIT 1

# ALL ROWS (work only in DML):


SELECT Name FROM Account WHERE isDeleted = true ALL ROWS

Operators:

# AND :


SELECT Id FROM Contact WHERE Name LIKE 'A%' AND MailingCity = 'California'

# OR :


SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'

# IN (Contain):


SELECT Id FROM Contact WHERE Firstname IN ( 'John' )


SELECT Id FROM Contact WHERE Firstname IN ( 'John', 'Raj' )

# NOT IN (not contain):


SELECT Firstname, Lastname FROM user WHERE firstname NOT IN ('John')


SELECT Name FROM Account WHERE BillingState NOT IN ('California', 'New York')

# LIKE :
Note: LIKE can not be applied on "TextArea","Id"
(Eg. A% : Start name from A
A% : End name with A
%A% : middle name with A )


SELECT Id FROM Contact WHERE Name LIKE 'A%'


SELECT Id FROM Contact WHERE Name LIKE '%A'


SELECT Id FROM Contact WHERE Name LIKE '%A%'

FIELDS() Functions:
The FIELDS() function lets you select groups of fields without knowing their names in advance.

Include any of these in the field list:

FIELDS(ALL) : to select all the fields of an object.
FIELDS(CUSTOM) : to select all the custom fields of an object.
FIELDS(STANDARD) : to select all the standard fields of an object.

Note: In each case, FIELDS() respects field-level security so it only shows the fields that you have permission to access.


SELECT FIELDS(ALL) FROM Account LIMIT 200


SELECT FIELDS(CUSTOM) FROM Account LIMIT 200


SELECT FIELDS(STANDARD) FROM Account


SELECT Id, FIELDS(ALL) FROM User LIMIT 150


SELECT Account.Name, (SELECT FIELDS(ALL) FROM Account.Contacts LIMIT 200) FROM Account

Multi-Select Picklists :
The INCLUDES and EXCLUDES operators are used to filtering the multi-select picklist field. The multi-select picklist field in Salesforce allows the user to select more than one value from the list of values provided.
# includes :
Includes (contains) the specified string. (country__c = custom field in Account object)


SELECT Id, country__c from Account WHERE country__c includes ('India','USA')

# excludes :
Excludes (does not contain) the specified string.


SELECT Id, country__c from Account WHERE country__c excludes ('USA')

Relationship in SOQL:

# For Standard relationship, It's the fieldname from child object (dot operator) and the field name from parent object.
1. Fetch Id, Name from Contact with Account Name and Billing City from Account Object


SELECT Id, Name, Account.Name, Account.BillingCity FROM Contact

# For Custom relationship, It's the field name from child object replaced __c with __r and the field name from Parent Object

1. Fetch Student Id, Name, Email from Student (custom object) and relationship with Contact (standard object)
Note : In custom relationship field must be with __r not __c (for related object require __r)


SELECT Id, Name, Email__c, Contact__r.AssistantName, Contact__r.AccountId FROM Student__c

# If you want fetch parent object id then use Contact__c only.


SELECT Id, Name, Email__c, Contact__r.AssistantName, Contact__c FROM Student__c

Note:
One parent have multiple child then [ . ] (dot operator will not work)

# Get Child relationship name:
Object manager => Fields and Relationships => Click on Lookup field name => Child Relationship Name
(eg. here 'Opportunities' is child relationship of Account Name [Lookup (Account)] field
1. To get details of all Accounts with Opportunities


SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Account

2. To get details of all Prospect Accounts with Opportunities


SELECT Id, Name, Type, (SELECT Id, Name FROM Opportunities) FROM Account WHERE Type = 'Prospect'

3. To get details of all Prospect Accounts with associated won Opportunities
SELECT Id, Name, Type, (SELECT Id, Name FROM Opportunities WHERE StageName = 'Closed Won') FROM Account WHERE Type = 'Prospect'
4. To get details of All Contact [Standard Object] with Student [Custom Object (master relationship with Contact object) ]
(eg. here 'Student' is child relationship of Contact__c [Master-Detail(Contact)] field
Note : In custom relationship field must be with __r not __c [ for related object require __r (eg. Students__r) ]
SELECT Id, Name, Email, (SELECT Id, Name, Email__c FROM Students__r) FROM Contact

# Some Examples :
1. Get all the Accounts for which NumberOfEmployees are greater than 500


SELECT Id, Name FROM Account WHERE NumberOfEmployees > 500

2. Get all the Opportunities that are closed today
SELECT Id, CloseDate FROM Opportunity WHERE CloseDate = 2022-05-10
SELECT Id, CloseDate FROM Opportunity WHERE CloseDate = TODAY

3. Get all the Accounts created in the last 5 Day ( Date Formats and Date Literals )


SELECT Id, CloseDate FROM Opportunity WHERE CloseDate = LAST_N_DAYS:5

4. Get All the Contacts having "John" in their name


SELECT Id, Name FROM Opportunity WHERE Name LIKE '%John%'

5. Get all the contacts that do not have "John" in their name


SELECT Id, Name FROM Opportunity WHERE NOT (Name LIKE '%John%')

6. Get all the Accounts for which NumberOfEmployees are more than 500 with an annualrevenue of atleast 1 million


SELECT Id, Name FROM Account WHERE NumberOfEmployees > 500 AND AnnualRevenue >= 1000000

# Or you can write this


SELECT Id, Name, NumberOfEmployees, AnnualRevenue FROM Account WHERE NumberOfEmployees > 500 AND AnnualRevenue >= 1000000

7. Get all the Accounts for which BillingCity is either of (NewYork, Chicago, Singapore)


SELECT Id, Name, BillingCity FROM Account WHERE BillingCity = 'NewYork' OR BillingCity = 'Chicago' OR BillingCity = 'Singapore'

# Or you can write this


SELECT Id, Name, BillingCity FROM Account WHERE BillingCity IN ('NewYork','Chicago','Singapore')

8. Get Id, Name and Amount of Won Opportunity having second Highest Amount


SELECT Name, Id, Amount, Probability FROM Opportunity WHERE StageName = 'Closed won' ORDER BY Amount DESC LIMIT 1 OFFSET 1

9. Get Id, Name and Billing Revenue (AnnualRevenue) of Account with 5th Highest Billing Revenue


SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue != null ORDER BY AnnualRevenue DESC LIMIT 1 OFFSET 4

10. Get the count of unique Billing Cities used in Account


SELECT COUNT_DISTINCT(BillingCity) FROM Account

11. Get count of all the Opportunities with Amount > 1000 grouped by StageName and show only for which count is at least 2


SELECT COUNT(Id), StageName FROM Opportunity WHERE Amount > 1000 GROUP BY StageName HAVING Count(Id) > 1

The following are examples of text searches that use SOQL.

# Relationship queries: child-to-parent


SELECT Contact.FirstName, Contact.Account.Name FROM Contact


SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = 'media'

# Relationship queries: parent-to-child


SELECT Name, (SELECT LastName FROM Contacts) FROM Account


SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account

# Relationship query with WHERE


SELECT Name, (SELECT LastName FROM Contacts WHERE CreatedBy.Alias = 'x') FROM Account WHERE Industry = 'media'

# Relationship query: child-to parent with custom objects


SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE 'C%'

# Relationship query: parent to child with custom objects


SELECT Name, (SELECT Name FROM Line_Items__r) FROM Merchandise__c WHERE Name LIKE 'Acme%'

# Relationship queries with aggregate


SELECT Name, (SELECT CreatedBy.Name FROM Notes) FROM Account


SELECT Amount, Id, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity

Notes:
Important Fact that needs to be considered when writing the SOQL

WHERE clauses cannot exceed 4,000 characters.
Total SOQL Queries used (Synchronous limit = 100)
Total SOQL Queries used (Asynchronous limit = 200)
Issued SOQL Queries 20
Issued DML Statements 150
The total amount of callouts per transaction 100
Allowed send email Methods 10
Apex transaction execution time 10 minutes.
SOQL query can’t run more than 120 seconds.
SOQL statements cannot exceed 20,000 characters.
Total number of records retrieved by SOQL queries: 50,000
SOQL query can be ORDER BY 32 fields.
No more than 20 relationships can be specified in a single query.
Maximum rows returned in SOQL query results : 2,000 results total (API version 28.0 and later), unless you specify custom limits in the query. This limit includes results from child objects. Previous API versions return 200 results.
Maximum length of SOSL statements : By default, 100,000 characters. This limit is tied to the SOQL statement character limit defined for your org.
In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact. Account. Owner. FirstName (three levels).
Long, complex SOQL statements, such as statements that contain many formula fields, can sometimes result in a QUERY_TOO_COMPLICATED error. The error occurs because the statement is expanded internally when processed by Salesforce, even though the original SOQL statement is under the 20,000 character limit. To avoid this error, reduce the complexity of your SOQL statement.
No more than 55 child-to-parent relationships can be specified in a query. A custom object allows up to 40 relationships, so you can reference all the child-to-parent relationships for a custom object in one query.
No more than 20 parent-to-child relationships can be specified in a query.
In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName (three levels).
In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specifies Account, the SELECT clause can specify only the Contact or other objects at that level. It could not specify a child object of Contact.
Refer official documentation : SOQL and SOSL Limits for Search Queries

Difference between SOQL and SOSL :

SOQL	SOSL
Salesforce Object Ouery Language	Salesforce Object Search Language
Used to retrive data from single object or multiple objects that are related with each other	Fetches data from multiple un-related objects
It returns records.	It returns fields.
Not more than 50,000 records can be fetched	Not more than 2000 records can be fetched.
SELECT Id, Name FROM Account	FIND {John}
Total number of SOQL queries issued Synchronous Limit : 100 Asynchronous Limit : 200	Total number of SOSL queries issued : 20
We can query on all fields of any data type.	We can query on fields whose data type is Name, Phone, Email.
Return Type: List	Return Type: List Of Lists.