SOQL Query in Salesforce Apex

SOQL Query in Salesforce Apex

On May 13, 2024, Posted by , In Salesforce Apex Tutorial, With Comments Off on SOQL Query in Salesforce Apex
SOQL Query Salesforce Apex
SOQL Query in Salesforce Apex

Table of Contents

What is SOQL in Salesforce?

SOQL stands for Salesforce Object Query Language. SOQL can be used to access information stored in your organization’s database. The syntax of SOQL is similar to that of SQL (Structured Query Language).SOQL queries can be written in Apex code or in the Query Editor of the Developer Console. In Soql records can only be searched on a single sObject if it meets the given criteria. Unlike SOSL, it cannot search across multiple objects but nested queries are supported in SOQL.

Code Example:

// Fetching the Records via SOQL
// Create a list to store the queried invoice records
List<apex_invoice__c> invoiceList = new List<apex_invoice__c>();

try {
    // Querying the invoice records with specific criteria
    // Selecting fields Id, Name, APEX_Customer__r.Name, APEX_Status__c
    // from the APEX_Invoice__c object where the record was created today
    // and the customer name is not 'Test'
    invoiceList = [SELECT Id, Name, APEX_Customer__r.Name, APEX_Status__c 
                   FROM APEX_Invoice__c 
                   WHERE CreatedDate = TODAY AND APEX_Customer__r.Name != 'Test'];

    // Printing the total number of fetched records
    System.debug('We have a total of ' + invoiceList.size() + ' records in the list');

    // Looping through each fetched record and printing its values
    for (apex_invoice__c objInvoice : invoiceList) {
        // Printing the record's values
        System.debug('Record value: ' + objInvoice);
    }
} catch (Exception e) {
    // Handling any exceptions that might occur during the query execution
    System.debug('An error occurred: ' + e.getMessage());
}

Explanation:

  1. Fetching Records via SOQL: We’re using a Salesforce Object Query Language (SOQL) query to retrieve records from the APEX_Invoice__c object.
  2. Query Criteria: We’re querying records created today ( CreatedDate = TODAY ) and whose customer name is not ‘Test’ ( APEX_Customer__r.Name != 'Test' ).
  3. Error Handling: We’ve added a try-catch block to catch any exceptions that might occur during the query execution.
  4. Printing Records: We’re printing the total number of fetched records and looping through each record to print its values using System.debug() . This helps in debugging and understanding the data retrieved from the query.

SQL (Structured Query Language) is the standard language for managing and manipulating databases. Here’s an overview of the basic syntax used in SQL to perform common operations such as selecting data, inserting records, updating records, and deleting records.

1. SELECT Statement

The SELECT statement is used to select data from a database. Data is retrieved from one or more tables.

Syntax:

SELECT column1, column2, ...
FROM tableName
WHERE condition;

2. INSERT INTO Statement

The INSERT INTO statement is used to insert new records into a table.

Syntax:

INSERT INTO tableName (column1, column2, ...)
VALUES (value1, value2, ...);

3. UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE tableName
SET column1 = value1, column2 = value2, ...
WHERE condition;

4. DELETE Statement

The DELETE statement is used to delete existing records from a table.

Syntax:

DELETE FROM tableName
WHERE condition;

5. CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database.

Syntax:

CREATE TABLE tableName (
    column1 dataType constraints,
    column2 dataType constraints,
    ...
);

Additional Clauses

  • WHERE: Specifies conditions for the SELECT , UPDATE , and DELETE statements.
  • ORDER BY: Orders the results according to one or more columns.
  • GROUP BY: Groups rows that have the same values in specified columns into summary rows.
  • HAVING: Specifies a condition on the groups being created.

CRS Info Solutions offers a comprehensive and dynamic Salesforce online course career building program for beginners, covering admin, developer, and LWC concepts. This course features immersive real-time projects, interactive hands-on learning, detailed daily notes, essential interview questions, thorough certification preparation, and strategic job prep guidance. Join their inspiring free demo to embark on an exciting Salesforce journey with expert mentorship and unlock your full potential in the Salesforce ecosystem. Enroll for a free demo today!

Next chapter is Security and previous chapter is SOSL Query.

SOQL Cheat Sheet

CommandDescriptionExample
SELECT Retrieves fields from specified objects. SELECT Name FROM Account
FROM Specifies the object from which to retrieve data. SELECT Name FROM Contact
WHERE Filters results based on specified conditions. SELECT Name FROM Account WHERE Industry = 'Finance'
LIMIT Restricts the number of rows returned. SELECT Name FROM Account LIMIT 5
ORDER BY Specifies the order of returned rows. SELECT Name FROM Account ORDER BY Name ASC
GROUP BY Groups results by one or more fields. SELECT Count(), Industry FROM Account GROUP BY Industry
HAVING Filters grouped records returned by a GROUP BY clause. SELECT Industry, Count(Id) FROM Account GROUP BY Industry HAVING Count(Id) > 5
LIKE Filters results by pattern matching. SELECT Name FROM Contact WHERE Email LIKE '%@example.com'
IN Filters results to include specified values in a field. SELECT Name FROM Account WHERE Industry IN ('Banking', 'Insurance')
INCLUDES Used in multi-select picklists to select values. SELECT Name FROM Account WHERE Industries__c INCLUDES ('Banking', 'Insurance')
EXCLUDES Used in multi-select picklists to exclude values. SELECT Name FROM Account WHERE Industries__c EXCLUDES ('Retail')
TYPEOF For polymorphic relationships, specifies different fields for different related objects. SELECT TYPEOF What WHEN Account THEN Phone, NumberOfEmployees WHEN Opportunity THEN Amount, StageName ELSE Name, Title END FROM Task
SOQL Cheat Sheet

This cheat sheet covers the basic syntax and common uses of SOQL commands to help with quick reference during development.

SOQL Variable Binding

SOQL (Salesforce Object Query Language) variable binding allows you to use a single set of query string and dynamically pass values to the query at runtime. This helps prevent SOQL injection and makes your queries more efficient.

Example:

Consider a simple SOQL query without variable binding:

String name = 'John';
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Name = :name];

In this example, :name is a bind variable that gets replaced with the value of the name variable when the query is executed. This approach helps protect against SOQL injection and allows for more flexible query construction.

Keywords Used In SOQL Queries

In Salesforce Object Query Language (SOQL), keywords play a crucial role in crafting precise and effective queries to retrieve data from Salesforce objects. These keywords, such as SELECT, FROM, WHERE, and ORDER BY, among others, provide the structure and logic necessary to filter, sort, and manipulate data according to specific criteria. Understanding and utilizing these keywords effectively is essential for Salesforce developers and administrators to extract meaningful insights and drive informed decision-making within their organizations.

SELECT: Retrieves one or more fields from an object.

SELECT Id, Name FROM Account

FROM: Specifies the object from which to retrieve records.

SELECT Id, Name FROM Account

WHERE: Filters records based on specified criteria.

SELECT Id, Name FROM Account WHERE Industry = 'Technology'

ORDER BY: Specifies the sorting order for the query results.

SELECT Id, Name FROM Account ORDER BY CreatedDate DESC

GROUP BY: Groups query results by specified fields.

SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry

HAVING: Adds additional filtering after GROUP BY.

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

LIMIT: Specifies the maximum number of records to return.

SELECT Id, Name FROM Account LIMIT 10

OFFSET: Skips a specified number of records before returning results.

SELECT Id, Name FROM Account LIMIT 10 OFFSET 5

IN: Specifies a list of values for a field.

SELECT Id, Name FROM Account WHERE Industry IN ('Technology', 'Finance')

NOT IN: Specifies a list of values not to include.

SELECT Id, Name FROM Account WHERE Industry NOT IN ('Retail', 'Healthcare')

LIKE: Performs a partial match search.

SELECT Id, Name FROM Account WHERE Name LIKE '%Corp%'

AND: Combines multiple conditions in a WHERE clause.

SELECT Id, Name FROM Account WHERE Industry = 'Technology' AND AnnualRevenue > 1000000

OR: Specifies multiple conditions, of which at least one must be true.

SELECT Id, Name FROM Account WHERE Industry = 'Technology' OR Industry = 'Finance'

FOR UPDATE: Locks the selected rows for update.

SELECT Id, Name FROM Account WHERE Name = 'Acme' FOR UPDATE

ALL ROWS: Allows querying of all records, including archived and deleted records

SELECT Id, Name FROM Account WHERE Name = 'Acme' ALL ROWS

Traversing Relationship Fields in SOQL

Let’s traverse relationship fields in Salesforce using an example. Suppose we have two custom objects: Account and Contact , where Contact has a lookup relationship to Account called Account__c . Here’s how we can traverse the relationship fields:

// Querying Contacts and their related Account information
List<Contact> contactsWithAccountInfo = [SELECT Id, Name, Email, Account__r.Name, Account__r.Industry 
                                         FROM Contact];

// Iterating through the queried contacts
for (Contact contact : contactsWithAccountInfo) {
    // Accessing contact fields
    String contactName = contact.Name;
    String contactEmail = contact.Email;

    // Accessing related Account fields
    // Using Account__r.Name to access the Name field of the related Account
    String accountName = contact.Account__r.Name;

    // Using Account__r.Industry to access the Industry field of the related Account
    String accountIndustry = contact.Account__r.Industry;

    // Printing contact and related Account information
    System.debug('Contact Name: ' + contactName);
    System.debug('Contact Email: ' + contactEmail);
    System.debug('Related Account Name: ' + accountName);
    System.debug('Related Account Industry: ' + accountIndustry);
}

Explanation:

  1. SOQL Query: We are querying Contact records along with their related Account information using a SOQL query. We use Account__r.Name and Account__r.Industry to access fields from the related Account object. The _r signifies that we are traversing a relationship.
  2. Iteration: We loop through the queried Contact records using a for loop.
  3. Accessing Fields: Within the loop, we access fields of both the Contact and its related Account using dot notation ( . ). For example, contact.Account__r.Name accesses the Name field of the related Account .
  4. Debugging: We print out information about each Contact and its related Account using System.debug() statements for debugging purposes.

Fetching Child Records in SOQL

Let’s fetch child records related to a parent record in Salesforce using an example. We’ll consider a scenario where we have two custom objects: Account (parent) and Contact (child), with a lookup relationship from Contact to Account . Here’s how we can fetch child records ( Contact ) related to a parent record ( Account ):

// Define the ID of the parent Account record
Id parentAccountId = 'Insert_Account_ID_Here'; // Replace with actual Account ID

// Querying child Contact records related to the parent Account
List<Contact> childContacts = [SELECT Id, Name, Email 
                               FROM Contact 
                               WHERE AccountId = :parentAccountId];

// Iterating through the queried child Contact records
for (Contact childContact : childContacts) {
    // Accessing child Contact fields
    String contactName = childContact.Name;
    String contactEmail = childContact.Email;

    // Printing child Contact information
    System.debug('Contact Name: ' + contactName);
    System.debug('Contact Email: ' + contactEmail);
}

Explanation:

  1. Parent Account ID: We define the ID of the parent Account record from which we want to fetch child Contact records. Replace 'Insert_Account_ID_Here' with the actual ID of the parent Account .
  2. SOQL Query: We use a SOQL query to retrieve child Contact records ( SELECT Id, Name, Email FROM Contact WHERE AccountId = :parentAccountId ). We filter the contacts based on the AccountId field, which represents the lookup relationship to the parent Account .
  3. Iteration: We loop through the queried child Contact records using a for loop.
  4. Accessing Fields: Within the loop, we access fields of each child Contact using dot notation ( . ). For example, childContact.Name accesses the Name field of the child Contact .
  5. Debugging: We print out information about each child Contact using System.debug() statements for debugging purposes.

Fetching Parent Record in SOQL

Let’s fetch the parent record related to a child record in Salesforce using an example. We’ll consider a scenario where we have two custom objects: Account (parent) and Contact (child), with a lookup relationship from Contact to Account . Here’s how we can fetch the parent record ( Account ) related to a child record ( Contact ):

// Define the ID of the child Contact record
Id childContactId = 'Insert_Contact_ID_Here'; // Replace with actual Contact ID

// Querying the parent Account record related to the child Contact
Contact childContact = [SELECT Id, Name, Email, AccountId, Account.Name, Account.Industry 
                        FROM Contact 
                        WHERE Id = :childContactId];

// Accessing parent Account fields from the related Contact record
String accountId = childContact.AccountId;
String accountName = childContact.Account.Name;
String accountIndustry = childContact.Account.Industry;

// Printing parent Account information
System.debug('Account ID: ' + accountId);
System.debug('Account Name: ' + accountName);
System.debug('Account Industry: ' + accountIndustry);

Explanation:

  1. Child Contact ID: We define the ID of the child Contact record for which we want to fetch the related parent Account record. Replace 'Insert_Contact_ID_Here' with the actual ID of the child Contact .
  2. SOQL Query: We use a SOQL query to retrieve the child Contact record along with the related parent Account information ( SELECT Id, Name, Email, AccountId, Account.Name, Account.Industry FROM Contact WHERE Id = :childContactId ). We use dot notation ( . ) to traverse the relationship and access fields from the parent Account object.
  3. Accessing Parent Fields: After executing the query, we access fields of the parent Account from the related Contact record using dot notation ( . ). For example, childContact.Account.Name accesses the Name field of the parent Account .
  4. Debugging: We print out information about the parent Account using System.debug() statements for debugging purposes.

Aggregate Functions in SOQL

Aggregate functions in Salesforce are used to perform calculations on a set of records and return a single result. Here’s an example of using aggregate functions in SOQL:

Suppose we have a custom object called Opportunity with fields Amount (representing the amount of the opportunity) and StageName (representing the stage of the opportunity). We want to calculate the total amount of all opportunities and the average amount of opportunities in each stage.

// Querying aggregate data using SOQL
List<AggregateResult> results = [SELECT StageName, SUM(Amount) totalAmount, AVG(Amount) avgAmount 
                                 FROM Opportunity 
                                 GROUP BY StageName];

// Iterating through the results
for (AggregateResult ar : results) {
    // Accessing aggregated data
    String stageName = (String)ar.get('StageName');
    Decimal totalAmount = (Decimal)ar.get('totalAmount');
    Decimal avgAmount = (Decimal)ar.get('avgAmount');

    // Printing the aggregated data
    System.debug('Stage: ' + stageName);
    System.debug('Total Amount: ' + totalAmount);
    System.debug('Average Amount: ' + avgAmount);
}

Explanation:

  1. SOQL Query with Aggregate Functions: We use a SOQL query to perform aggregate functions on the Opportunity object. We select the StageName field and calculate the sum of Amount (totalAmount) and the average of Amount (avgAmount). We group the results by the StageName field.
  2. AggregateResult: The query returns a list of AggregateResult objects, each containing the aggregated data for a specific stage.
  3. Accessing Aggregated Data: We iterate through the AggregateResult objects using a for loop and access the aggregated data using the get() method.
  4. Printing: We print out the stage name, total amount, and average amount for each stage using System.debug() statements.

Binding Apex Variables in SOQL

Binding Apex variables, also known as binding variables or bind expressions, are used to dynamically pass values into SOQL queries, SOSL searches, and dynamic SOQL queries in Salesforce. They help prevent SOQL injection and improve performance. Here are examples of how to use binding variables in various contexts:

Static SOQL Query with Binding Variable:

String searchKey = 'Test'; // Value to search for
List<Contact> contacts = [SELECT Id, Name, Email FROM Contact WHERE LastName = :searchKey];

In this example, searchKey is a binding variable. It is used in the SOQL query to dynamically filter contacts by the last name specified in the searchKey variable.

String searchKey = 'Test'; // Value to search for
List<Contact> contacts = [SELECT Id, Name, Email FROM Contact WHERE LastName = :searchKey];

Dynamic SOQL Query with Binding Variable:

String objectType = 'Contact'; // Object type to query
String fieldName = 'LastName'; // Field name to filter
String searchKey = 'Test'; // Value to search for

String soqlQuery = 'SELECT Id, Name, Email FROM ' + objectType + ' WHERE ' + fieldName + ' = :searchKey';
List<SObject> records = Database.query(soqlQuery);

In this example, we construct a dynamic SOQL query using variables objectType , fieldName , and searchKey . The searchKey variable is bound to the query using the : syntax.

SOSL Search with Binding Variable:

String searchKey = 'Test'; // Value to search for
List<List<SObject>> searchResults = [FIND :searchKey IN ALL FIELDS RETURNING Contact(Id, Name, Email)];

Here, searchKey is bound to the SOSL search query using the :searchKey syntax. This searches for the specified keyword in all fields of the Contact object.

DML Statements with Binding Variables:

Account accToUpdate = new Account(Name = 'Example Account');
update accToUpdate;

In DML statements like update , insert , delete , and upsert , binding variables are used implicitly. In this example, accToUpdate is bound to the update operation, and its changes are persisted to the database.

Binding variables help make queries and DML operations dynamic, secure, and efficient by allowing values to be passed dynamically into queries and DML statements, thus preventing SOQL injection attacks and improving performance.

How to start with SOQL?

To work on SOQL you must have records in the database. So, before starting working on Soql follow the below steps to insert some records via apex.

Step 1: Open the developer console and click on the Debug tab to open the anonymous window as shown below the image.

Step 2: In the image below, we have created an Account and associated contact and opportunity with it via apex.

Using the Query Editor, we can now write and execute a SOQL query to fetch the above-inserted records. A SOQL query searches for data in a specific object and optionally, you may add a condition to the WHERE clause to filter your search.

In the image below we have queried the Account and its associated contact and opportunity records and filtered the result by Account Name.

Key Features: 

  1. SOQL allows you to query records from Standard and Custom objects in one go.
  2. SOQL allows querying on a single object or multiple objects that are related to one another. 
  3. SOQL supports aggregate functions just like SQL. We can roll up and summarize data with aggregate functions. For example, Avg, Min, Max, etc.
  4. SOQl allows binding apex variables in the query to filter the records.
  5. SOQl supports Parent to Child and Child to Parent queries.

Frequently Asked Questions

What are the differences between SOQL, SOSL, and SQL?

SOSL, or Salesforce Object Search Language, is like a powerful tool I use to find stuff in Salesforce. Imagine I’m searching for a specific word in a big library full of books. SOSL helps me do just that. It’s like a magic spell that lets me search across multiple books, not just one, to find what I need. So, instead of looking through each book one by one, SOSL quickly sifts through all the books and shows me the ones that mention the word I’m looking for. It’s super handy when I need to find information fast and don’t want to spend all day searching.

SOQL and SOSL are like cousins to SQL, but not identical twins. They speak a similar language, but they have their own quirks tailored specifically for Salesforce. Imagine SQL as the language you use to talk to a big library full of books, while SOQL and SOSL are the languages you use to talk to a specialized library, like a library of comic books or recipe books. They understand each other to some extent, but they also have their own unique words and phrases that make them different.

So, while you might recognize some similarities between them, they’re not exactly the same, just like cousins who share a family resemblance but have their own distinct personalities. SOQL and SOSL are designed for querying data within the Salesforce platform, each serving a specific purpose: SOQL for querying records from Salesforce objects and SOSL for performing full-text searches across multiple objects.

Examples:

SOQL Query:

SELECT Id, Name, Account.Name FROM Contact WHERE Account.Name = 'ACME'

This SOQL query retrieves the Id, Name, and the related Account’s Name of all contacts where the related Account’s Name is ‘ACME’.

SOSL Query:

FIND {ACME} IN ALL FIELDS RETURNING Contact(Id, Name), Account(Name)

This SOSL query searches for the keyword ‘ACME’ across all fields of the Contact and Account objects and returns matching Contact records with their Id and Name, as well as matching Account records with their Name.

SQL Query:

SELECT id, first_name, last_name FROM employees WHERE department = 'Sales'

This SOSL query searches for the keyword ‘ACME’ across all fields of the Contact and Account objects and returns matching Contact records with their Id and Name, as well as matching Account records with their Name.

How to write a basic SOQL Query?

To write a basic SOQL (Salesforce Object Query Language) query, you can use the SELECT statement followed by the fields you want to retrieve from a Salesforce object. Here’s a basic example of a SOQL query:

const query = 'SELECT Id, Name, AccountNumber FROM Account';

In this example, the query retrieves the Id , Name , and AccountNumber fields from the Account object in Salesforce.

You can execute this query using Salesforce’s JavaScript Remoting, Apex, or other Salesforce API methods depending on your use case. For example, using JavaScript Remoting, you would define a remote action in your Apex controller and call it from your JavaScript code:

Apex Controller:

public with sharing class AccountController {
    @RemoteAction
    public static List<Account> getAccounts() {
        return [SELECT Id, Name, AccountNumber FROM Account];
    }
}

JavaScript

Visualforce.remoting.Manager.invokeAction(
    '{!$RemoteAction.AccountController.getAccounts}',
    function(result, event) {
        if (event.status) {
            // Process the result
            console.log(result);
        } else {
            console.error(event.message);
        }
    }
);

This is a basic example, and SOQL queries can be more complex, including conditions ( WHERE clause), ordering ( ORDER BY ), and grouping ( GROUP BY ).

To write a basic SOQL (Salesforce Object Query Language) query, you first specify the fields you want to retrieve using the SELECT clause. For instance, let’s say we want to retrieve the “Name” and “Industry” fields from the “Account” object. We would write the SELECT clause like this: “SELECT Name, Industry”. This tells Salesforce to return the “Name” and “Industry” fields in the query results.

Next, we specify the object from which we want to retrieve the data using the FROM clause. In this example, we want to retrieve data from the “Account” object, so we write “FROM Account”. This indicates that we want to query data from the “Account” object specifically.

Putting it all together, our basic SOQL query looks like this: “SELECT Name, Industry FROM Account”. This query tells Salesforce to retrieve the “Name” and “Industry” fields from the “Account” object. It’s a simple yet powerful way to extract specific information from Salesforce objects, and it forms the foundation for more complex queries involving additional clauses like WHERE, ORDER BY, GROUP BY, and others.

How to find Object and Field API Names for your Queries?

To find Object and Field API Names for your queries in Salesforce, you can utilize several methods. One common approach is to navigate to the Object Manager within the Salesforce Setup menu. From there, you can search for the specific object you’re interested in, such as “Account” or “Contact.” Once you locate the object, click on it to access its details. Here, you’ll find a list of all the fields associated with that object, along with their respective API Names. These API Names serve as unique identifiers for each field and are crucial for referencing them accurately in your SOQL queries.

By leveraging these methods, you can efficiently discover Object and Field API Names required for your SOQL queries in Salesforce. Whether you’re a Salesforce administrator, developer, or consultant, understanding how to find and utilize Object and Field API Names is essential for effectively querying and manipulating data within the Salesforce platform.

Using Developer Console:

  • Navigate to the Developer Console by clicking on your profile picture and selecting “Developer Console”.
  • Open the “Execute Anonymous Window” by clicking on “Debug” > “Open Execute Anonymous Window”.
  • Enter the following Apex code to describe the Account object and its fields:
Schema.DescribeSObjectResult accountDescribe = Account.sObjectType.getDescribe();
Map<String, Schema.SObjectField> fieldsMap = accountDescribe.fields.getMap();
for(String fieldName : fieldsMap.keySet()) {
    System.debug('Field Label: ' + fieldsMap.get(fieldName).getDescribe().getLabel());
    System.debug('API Name: ' + fieldsMap.get(fieldName).getDescribe().getName());
}

Click on “Execute” to run the code. The Developer Console will display the Field Label and API Name for each field of the Account object.

To find the Object and Field API Names for your queries in Salesforce, you can use the Schema class in Apex. Here’s a code snippet that demonstrates how to retrieve the API Names of an object and its fields:

// Specify the object API name for which you want to retrieve the schema
String objectApiName = 'Account';

// Get the schema describe result for the object
Schema.DescribeSObjectResult describeResult = Schema.getGlobalDescribe().get(objectApiName).getDescribe();

// Retrieve the object API name
String objectName = describeResult.getName();
System.debug('Object API Name: ' + objectName);

// Retrieve the field API names
List<String> fieldApiNames = new List<String>();
for (Schema.SObjectField field : describeResult.fields.getMap().values()) {
    fieldApiNames.add(field.getDescribe().getName());
}
System.debug('Field API Names: ' + fieldApiNames);

This code snippet demonstrates how to retrieve the API Name of an object ( Account in this case) and its fields. You can modify the objectApiName variable to specify the object for which you want to retrieve the schema.

How to use SOQL in Apex?

In Apex, SOQL (Salesforce Object Query Language) is employed to retrieve records from Salesforce objects. SOQL queries in Apex are expressed as strings and can be executed using various methods provided by the Salesforce platform. One common approach is to write a static SOQL query directly within your Apex code. For example, you can use a static query to fetch a list of Account records where the Industry field is set to ‘Technology’. This involves writing the query directly within square brackets and assigning the result to a list variable, as shown:

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];

In addition to static queries, you can construct SOQL queries dynamically at runtime using string concatenation or by leveraging the Database.query() method. This approach allows you to build queries based on dynamic criteria. For instance, you might define query conditions based on user input or runtime variables. Dynamic queries enable greater flexibility in querying data from Salesforce objects within your Apex code. Here’s an example of constructing a dynamic SOQL query:

String industry = 'Technology';
String soqlQuery = 'SELECT Id, Name FROM Account WHERE Industry = \'' + industry + '\'';
List<Account> accounts = Database.query(soqlQuery);

Furthermore, SOQL supports the use of binding variables, which allow you to dynamically pass values into the query at runtime. Binding variables are denoted by a colon followed by the variable name. This helps prevent SOQL injection and enhances query security. Here’s how you can use binding variables in a SOQL query:

String industry = 'Technology';
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = :industry];

Moreover, SOQL allows you to query fields on related objects using relationship fields. For example, you can query fields on child objects using parent-child relationship fields. Once you have executed a SOQL query, you can iterate through the query results using standard Apex iteration methods like for loops. This enables you to process and manipulate the retrieved data as needed within your Apex code. By leveraging SOQL in Apex, you can effectively interact with Salesforce data and build robust applications on the Salesforce platform.

Difference between SOQL and SOSL

SOQL (Salesforce Object Query Language)SOSL (Salesforce Object Search Language)
Used for querying data from specific objects and their related objects.Used for performing text searches in records across multiple objects.
Allows querying of individual records similar to SQL SELECT statements.Allows searching of fields with a search expression using keywords.
Returns records that meet specific criteria defined in the WHERE clause.Returns records containing the search term, potentially from multiple object types simultaneously.
Query targets a specific object or a specific set of objects defined in the FROM clause.Searches the global index of all records that meet the search criteria across multiple objects.
Supports nested queries and relationship queries.Limited to simple search conditions; does not allow nested queries or relationship queries.
Ideal for precise, complex queries where you know the structure of the database.Ideal for broad, text-based searches where the exact location of data is unknown.
Can include COUNT, SUM, MAX, MIN, and other functions.Primarily focuses on finding text in record fields without aggregate functions.
Has a row limit based on context, usually 50,000 records in batch Apex.Has a different limit, typically 2,000 records for global search across objects.

For those looking for Salesforce learning, CRS Info Solutions provides an extensive Salesforce training program designed to enhance your skills and career opportunities. Explore our Salesforce training in India to gain practical, hands-on experience. Our training covers all essential aspects of Salesforce, ensuring comprehensive learning. With expert instructors and a detailed curriculum, CRS Info Solutions is committed to your success in the Salesforce ecosystem with our Career Building program. Whether you are a beginner or looking to advance your skills, they offer the guidance and resources you need. Enroll for free demo today!

Next chapter is Security and previous chapter is SOSL Query.

Comments are closed.