What is SOQL and how to use it in Salesforce Apex?

SOQL (Salesforce Object Query Language) is a query language used in Salesforce to retrieve data from Salesforce objects, similar to SQL for relational databases. It allows developers to query records from standard and custom objects, including their fields and related records, in a structured and efficient way. SOQL is primarily used in Apex (Salesforce’s programming language) to fetch data for processing or display. This post will explain SOQL for Salesforce Developers.

Key characteristics of SOQL:

• Read-only: SOQL is used to retrieve data, not to modify it (use DML for updates, inserts, etc.).
• Object-oriented: Queries target Salesforce objects (standard like Account, Contact, or custom objects).
• Supports relationships: SOQL can traverse parent-to-child or child-to-parent relationships using dot notation.
• Governor limits: SOQL queries are subject to Salesforce governor limits (e.g., 50,000 records returned per transaction).

Using SOQL in Apex

SOQL queries can be embedded in Apex code to retrieve data dynamically. They are executed using Apex’s query methods, and the results are typically stored in variables like Lists, Sets, or Maps for further processing.

Syntax

A basic SOQL query in Apex looks like this:

List<ObjectName> records = [SELECT Field1, Field2 FROM ObjectName WHERE Condition ORDER BY Field LIMIT Number];

SELECT: Specifies the fields to retrieve.

FROM: Specifies the object to query (e.g., Account, Contact, or custom object like MyObject__c).

WHERE: Filters records based on conditions.

ORDER BY: Sorts results by a field.

LIMIT: Restricts the number of records returned.

Steps to Use SOQL in Apex

1. Write the SOQL Query:
   • Identify the object and fields you need.
   • Add conditions (WHERE), sorting (ORDER BY), or limits (LIMIT) as needed.
   • Use relationship queries for related objects (e.g., SELECT Name, Account.Name FROM Contact).
2. Execute the Query in Apex:
   • Use square brackets [ ] for inline SOQL queries.
   • Store results in a variable (e.g., List<Account> for multiple records or Account for a single record).
   • Alternatively, use Database.query() for dynamic SOQL queries.
3. Process the Results:
   • Iterate over the results using loops (e.g., for loops).
   • Access fields using dot notation (e.g., record.Name).
4. Handle Exceptions:
   • Use try-catch blocks to handle QueryException for invalid queries or no results.
5. Follow Governor Limits:
   • Avoid querying too many records (limit 50,000 per transaction).
   • Use selective queries (e.g., filter by indexed fields like Id or Name) to optimize performance.

Examples

1. Basic SOQL Query in Apex: Retrieve a list of Accounts where the industry is 'Technology'.
   List<Account> accounts = [SELECT Id, Name, Industry FROM Account WHERE Industry = 'Technology' LIMIT 10];

   for (Account acc : accounts) {

   System.debug('Account Name: ' + acc.Name + ', Industry: ' + acc.Industry);

   }
2. Relationship Query (Parent-to-Child): Retrieve Contacts and their related Account names. You need to use the relationship name for the parent object. Standard parent objects typically have the same name, but custom objects have a different name. It can be seen lookup field.
   List<Contact> contacts = [SELECT FirstName, LastName, Account.Name FROM Contact WHERE AccountId != null LIMIT 5];

   for (Contact con : contacts) {

   System.debug('Contact: ' + con.FirstName + ' ' + con.LastName + ', Account: ' + con.Account.Name);

   }
3. Dynamic SOQL with Database.query(): Build a query dynamically based on a variable. It can be used when you want to generate SOQL at runtime. 
   String industry = 'Technology';

   String query = 'SELECT Id, Name FROM Account WHERE Industry = :industry LIMIT 10';

   List<Account> accounts = Database.query(query);

   for (Account acc : accounts) {

   System.debug('Account Name: ' + acc.Name);

   }
4. Handling Single Record: Retrieve a single Account by ID.
   try {

   Account acc = [SELECT Id, Name FROM Account WHERE Id = '001xxxxxxxxxxxx' LIMIT 1];

   System.debug('Account Name: ' + acc.Name);

   } catch (QueryException e) {

   System.debug('No account found: ' + e.getMessage());

   }
5. Aggregate Query: Count the number of Contacts per Account.
   List<AggregateResult> results = [SELECT AccountId, COUNT(Id) contactCount FROM Contact GROUP BY AccountId];

   for (AggregateResult ar : results) {

   System.debug('Account Id: ' + ar.get('AccountId') + ', Contact Count: ' + ar.get('contactCount'));

   }

Best Practices for SOQL

• Optimize Queries:
  • Use selective filters (e.g., indexed fields like Id, Name, or custom indexed fields).
  • Avoid querying unnecessary fields to reduce payload size.
• Avoid Hardcoding IDs:
  • Use dynamic queries or custom settings for IDs to make code reusable. We always avoid hardcoded id.
• Handle Null Results:
  • Check if the query returns records before processing (e.g., if (!accounts.isEmpty())).
• Use Bind Variables:
  • Prevent SOQL injection by using bind variables (e.g., :industry) in dynamic queries.
• Limit Records:
  • Use LIMIT to avoid hitting governor limits, especially in loops or triggers.
• Test Queries:
  • Test SOQL queries in the Salesforce Query Editor (Developer Console) before embedding in Apex.

Common Use Cases

• Triggers: Query related records to enforce business logic (e.g., validate data).
• Batch Apex: Process large datasets by querying records in chunks.
• Visualforce Controllers: Fetch data to display on custom pages.
• Lightning Components: Retrieve data for dynamic UIs via Apex controllers.

Resources

• Salesforce Documentation: SOQL and SOSL Reference
• Apex Developer Guide: Apex Developer Guide
• Query Editor: Use the Developer Console or Workbench to test SOQL queries.