Back

SOQL - Salesforce Object Query Language

SOQL stands for Object Query Language for Salesforce. SOQL can be used to read information stored in the database of your org. SOQL (Structured Query Language) is syntactically similar to SQL. You can write and run a SOQL query in Apex code or in Query Editor of the Developer Console.

When to Use SOQL

You can 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.

Syntax of SOQL

SELECT one or more fields 
FROM an object 
WHERE filter statements and, 
optionally, results are ordered

Common Use cases of SOQL

Basic SOQL Query

This will return the Id and Name fields from the Account table. No filtering or sorting will be applied.

SELECT Id, Name FROM Account
Dynamic SOQL

You can execute a database query from a String rather than a regular SOQL expression:

String tableName = 'Account';
String queryString = 'SELECT Id FROM ' + tableName + ' WHERE CreatedDate >= YESTERDAY';
List<SObject> objects = Database.query(queryString);

Since dynamic SOQL queries are not compiled, their schema references are not validated, so it is preferable to use Apex variable interpolation using the :variable syntax where possible.

Potential Exceptions in Apex SOQL Queries

When assigning to a single object, a query that returns anything other than a single row will throw a QueryException.

try {
    Account a = [SELECT Id FROM Account WHERE Name = 'Non-existent Account'];  
} catch (QueryException e) {
    // List has no rows for assignment to SObject
}

try {
    Account a = [SELECT Id FROM Account];  
} catch (QueryException e) {
    // List has more than 1 row for assignment to SObject
}

Attempting to use a field that you did not include in the query will throw a SObjectException

Account a = [SELECT Id FROM Account LIMIT 1];
try {
    System.debug( a.Name );
} catch (SObjectException e) {
    // SObject row was retrieved via SOQL without querying the requested field: Name
}
SOQL Queries in Apex

To perform a query in Apex, surround the query with square brackets. The result can be assigned to a list, or to a single object.

List<Account> allAccounts = [SELECT Id, Name FROM Account];
Account oldestAccount = [SELECT Id, Name FROM Account ORDER BY CreatedDate LIMIT 1];
SOQL Query to Reference Parent Object's Fields

When object's are linked by a lookup or master-detail relationship, the parent records field's can be referenced from the child record or 'base object' in a query. This is also known as upwards traversal.

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

It's possible to traverse five records upwards.

SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact

When the base object is a custom lookup field, the __c in field's name Primary_Influencer__c, for example, will be changed to __r.

SELECT Primary_Influencer__r.Nickname__c FROM Contact

SOQL Query to get child Records

SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account
SOQL Query With Filtering

This will return the name of all active Users.

SELECT Name FROM User WHERE IsActive = true

This will return Contacts created on or after January 1st, 2016.

SELECT Name, Phone FROM Contact WHERE CreatedDate >= 2016-01-01T00:00:00.000Z

This will return the first 100 Accounts from an unordered list.

SELECT Id, Name FROM Account LIMIT 100

This will return Leads with a phone number matching the specified format. '%' acts as a wild card character. Using LIKE '% %' also enables a developer to replicate a CONTAINS() formula.

SELECT Id, Name, Phone FROM Lead WHERE Phone LIKE '(%) %-%'

Will return Leads with a lead source that contains Google i.e. 'Google AdWords' & 'Google Natural Search'.

SELECT Email FROM Lead WHERE LeadSource LIKE '%Google%'
SOQL Query With Ordering

This Query return the Name in Ascending orders.Default order is ascending.

SELECT Id, Name FROM User ORDER BY LastName

This query will returns the Name in Descending order because we have used ORDER BY Clause called DESC

SELECT Id, Name FROM Contact ORDER BY LastModifiedDate DESC

This query will returns the Name and Title in Ascending order along with null records at the beginning because we have used ORDER BY Clause called ASC NULLS FIRST

SELECT Name, Title FROM User ORDER BY Title ASC NULLS FIRST

You can provide order by fields separated by comma. You can command position of null if there are null values. You can write NULLS FIRST or 'NULLS LAST' after order by field

SELECT Id FROM Contact ORDER BY LastName ASC NULLS LAST, FirstName ASC NULLS FIRST
Using a Semi-Join

Selecting all accounts that have open opportunity records under them

SELECT Id, Name FROM Account WHERE AccountId IN 
(SELECT Id FROM Opportunity WHERE IsClosed = false)
Using SOQL to Construct a Map

A very useful feature many people overlook is the ability to construct a Map using a SOQL query.

Map<Id, Account> accounts = new Map<Id, Account>([SELECT Id, Name FROM Account]);
                        System.debug(accounts);

When you run this code, accounts then contains a Map of your Account objects, keyed on Id. The output to the debug log would look similar to this:

11:15:10:025 USER_DEBUG [13]|DEBUG|{
    XXXXXXXXXXXXXXXXXX=Account:{Id=XXXXXXXXXXXXXXXXXX, Name=Account 1}, 
    YYYYYYYYYYYYYYYYYY=Account:{Id=YYYYYYYYYYYYYYYYYY, Name=Account 2}, 
    ZZZZZZZZZZZZZZZZZZ=Account:{Id=ZZZZZZZZZZZZZZZZZZ, Name=Account 3}, 
    ...
}

You are now able to look up the Account objects using their Id. Furthermore, if you want a collection of unique IDs, you can call the keySet() function of the Map class, like so:

System.debug(accounts.keySet());

which looks something like this in the debug log:

11:23:21:010 USER_DEBUG [15]|DEBUG|{XXXXXXXXXXXXXXXXXX, YYYYYYYYYYYYYYYYYY, ZZZZZZZZZZZZZZZZZZ, ...}

This is very useful when you need to query to get records and access them repeatedly in your code.

Variable References in Apex SOQL Queries

To reference a variable in a query, add a colon (:) before the variable name.

Datetime targetDate = Datetime.now().addDays(-7);
List<Lead> recentLeads = [SELECT Id FROM Lead WHERE CreatedDate > :targetDate];

string targetName = 'Unknown';
List<Contact> incompleteContacts = [SELECT Id FROM Contact WHERE FirstName = :targetName];
Back
Site developed by Nikhil Karkra © 2021 | Icons made by Freepik