SOQL in Salesforce

Salesforce allows you to retrieve the object records information from its database through SOSL and SOQL. In most of the scenarios, if you want to perform data manipulation statically or through customization (like Apex, etc), you need to know the records of a particular object in which the Data Manipulation is performed. In this scenario, SOSL and SOQL come into picture. In this guide, we will discuss about SOQL and utilize it in Apex class in different scenarios.

1. SOQL – SELECT
2. SOQL – WHERE Clause
3. SOQL – Relationships
4. SOQL in Apex

Salesforce Object Query Language (SOQL)

SOQL stands for Salesforce Object Query language which retrieves all/specific records from Salesforce on a particular object. By specifying the single object, we can get the records of another object which is related to this specified object. Only 50,000 records were retrieved by the SOQL query on an object. Before going to the structure of SOQL, we will see how to run the SOQL queries in the Salesforce Org.

1. Open the Salesforce org and click on the gear icon. Select the “Developer Console”.

2. A new window will open where you can write the SOQL queries in the Query Editor.
3. Click on the Query Editor.
4. A Console will open under this to write the SOQL Query.
5. After writing the query, click on the “Execute” button.
6. If the query is fine without any error, the records with respect to the query will be displayed at the top console.
7. If the query fails, the errors are displayed below the Query Editor.

SOQL – SELECT

SOQL query starts with a SELECT keyword followed by the fields of an object. After specifying the field/s, the FROM keyword is used to retrieve these fields from an object.

Syntax:

1. For particular fields:

2. To select all the fields that are present in the object: Pass ALL inside the FIELDS() keyword.

3. To select all the custom fields that are present in the object: Pass CUSTOM inside the FIELDS() keyword.

4. To select all the standard fields that are present in the object: Pass STANDARD inside the FIELDS() keyword.

Let’s see some SOQL queries that use the SELECT keyword.

Example 1:

Let’s query on the “Campaign” object to get all fields. Retrieve only two records.

Using the “LIMIT”, we returned the first two records only.

Example 2:

Let’s query on the “Contact” object to get all the custom fields.

There are only five custom fields that are present in the “Contact” object and we returned the first 10 records only.

Example 3:

Let’s query on the “Lead” object to get the first 10 records with the Id, FirstName, LastName, Title, and Company fields.

SOQL – WHERE

To filter the particular records in the Salesforce system, we can use the WHERE clause along with  SELECT. In this way, we can filter the records by specifying some conditions with the WHERE Clause.

Syntax:

Let’s discuss some scenarios in which we can specify the conditions with some operators.

Scenario 1: WHERE with Comparison Operators

We can refer to =, !=, <, >, <=, >=, IN, NOT IN and LIKE as comparison operators. These will compare the field values with specified values using the previous comparison operators.

Example 1:

Let’s query on the “Lead” object by filtering the records where the “NumberOfEmployees” is greater than 5000.

Query Results:

Here, we specified the condition using the “>” operator. Only one record is returned by the query that matches the condition.

Example 2:

Query on the same object as part of Example 1 by filtering the records where the “Country” are “USA”, “Japan”, and “France”.

Query Results:

There are 21 records with “USA”, “Japan”, and “France” countries.

Example 3:

Now, use the LIKE operator to display the records with the “Title” that includes “Manager”.

Query Results:

Only one record includes the “Title” as “Manager”.

Scenario 2: WHERE with Logical Operators

We can refer to AND, OR, and NOT as logical operators.

1. AND returns the record/s if both the conditions are True.
2. OR returns the record/s if any of the conditions meets the criteria.
3. NOT returns the record/s that don’t match the condition.

Example 1:

Let’s query on the “Account” object by filtering the records where the “Industry” is “Energy” and the “Rating” is “Hot”.

Query Results:

Only one record is returned by the query that matches both the conditions.

Example 2:

Let’s modify the previous query on the “Account” object by filtering the records where the “Industry” is “Energy” or the “Rating” is “Hot” by specifying the OR operator.

Query Results:

Scenario 3: WHERE with Date Literals

It can be possible to filter the object records based on date literals. Suppose you want the records that were created yesterday, we can use the YESTERDAY date literal. Let’s discuss some useful date literals. We can specify the condition within the WHERE clause.

1. YESTERDAY – It returns the records that were created/updated before the current day.
2. TODAY – It returns the records that were created/updated in the current day.
3. LAST_WEEK – It returns the records that were created/updated in the last week.
4. THIS_WEEK – It returns the records that were created/updated in this week.
5. LAST_MONTH – It returns the records that were created/updated in the last month.
6. THIS_MONTH – It returns the records that were created/updated in this month.
7. LAST_N_DAYS:n – It returns the records that were created/updated “n” days ago.
8. LAST_N_WEEKS:n – It returns the records that were created/updated “n” weeks ago.
9. THIS_YEAR – It returns the records that were created/updated in this year.
10. LAST_YEAR – It returns the records that were created/updated in the last year.

Example 1:

Select the Id, Name, and CreatedDate fields from the “Campaign” object which were created yesterday.

Query Results:

There are four records that were created yesterday.

Example 2:

Select the Id, Name, and CreatedDate fields from the “Campaign” object which were created today.

Query Results:

No record was created today. So, the query returned 0 rows.

Example 3:

Select the Id, Name, and ClosedDate fields from the “Opportunity” object in which the CloseDate is created within 10 days.

Query Results:

There are four records as CloseDate within 10 days.

SOQL – Relationships

SOQL allows you to query the parent fields on the child object and child fields on the parent object.

Syntax for Standard Objects:

1. Parent – Child:

In this relation, the specified object in the SOQL query should be the “Parent” object. Within this, we need to write another SOQL query on the “Child” object with the fields that are surrounded by the braces.

2. Child – Parent:

In this relation, the specified object in the SOQL query should be the “Child” object. We need to specify the parent object along with its field along with child object fields.

Syntax for Custom Objects:

1. Parent – Child:

Here, we need to specify the child object that ends with the “__r” suffix.

2. Child – Parent:

In this case, we need to specify the parent object that ends with the “__r” suffix.

Let’s see the example queries on standard objects.

Example 1: Parent to Child Relationship

We know that the “Account” object is the parent object to the “Contacts” object. Let’s get the Id, Name, and Industry fields from the “Account” object. After that, get the “Title” and “LastName” fields from the “Contact” object.

There are 14 accounts that hold the contacts. For each account, the contacts with the “Title” and “LastName” were returned for each account.

Example 1: Parent to Child Relationship

We know that the “Account” object is the parent object to the “Contacts” object. Let’s get the Id, Name, and Industry fields from the “Account” object. After that, get the “Title” and “LastName” fields from the “Contact” object.

There are 14 accounts that are associated with contacts. For each account, the contacts with the “Title” and “LastName” were returned for each account.

Example 2: Child to Parent Relationship

Let’s query on the “Contacts” object. Get the contact Id, LastName, and its parent Account Industry.

There are 21 contacts that are associated with the account.

SOQL in Apex

In this part, we will see how to execute the SOQL queries through the Apex class. Apex supports the Map, List, and Set Data structures. We can utilize these data structures and execute the SOQL queries by creating them.

List – The records are returned in the (Object:{Fields}) format.

Map – The records are returned in the {Id=Object:{Fields}} format.

Set – The records are returned in the {Object:{Fields}} format.

Example 1:

Create a list named “Lead_obj” and pass an SOQL query that selects the “Title” and “Country” which is “Japan” from the “Lead” object.

Output:

The record that is returned by the list is (Lead:{Title=CFO, Country=Japan, Id=00Q5i00000I9rhxEAB}).

Example 2:

Let’s execute the same query using the map in Apex.

Output:

The record that is returned by the map is {00Q5i00000I9rhxEAB=Lead:{Title=CFO, Country=Japan, Id=00Q5i00000I9rhxEAB}}.

Conclusion

SOQL stands for Salesforce Object Query language which retrieves all/specific records from Salesforce on a particular object. In this guide, we learned how to write and execute the SOQL query with different keywords like FIELDS() and WHERE. As part of WHERE, different conditions are specified using the logical operators, comparison operators, and date literals. Lastly, we learned how to execute these queries with Apex data structures with list and map.