SOQL Queries in Apex

SOQL Queries in Apex: A Comprehensive Guide for Salesforce Platform Developer 1 Exam

Why SOQL Queries in Apex Matter

SOQL (Salesforce Object Query Language) is one of the most fundamental skills a Salesforce developer must master. It is the primary mechanism for retrieving data from the Salesforce database within Apex code. Nearly every meaningful Apex application — whether it is a trigger, a batch job, a controller, or a utility class — relies on SOQL to read records. On the Salesforce Platform Developer 1 exam, SOQL Queries in Apex is a heavily tested topic because it sits at the intersection of two critical domains: Process Automation and Logic and Data Modeling and Management. Understanding how to write, optimize, and properly use SOQL in Apex is essential not only for passing the exam but for building real-world Salesforce solutions.

What Are SOQL Queries in Apex?

SOQL is a query language specifically designed for the Salesforce platform. Unlike SQL, which works with traditional relational databases, SOQL is tailored to work with Salesforce's multi-tenant architecture and object-relationship model. When embedded directly in Apex code, SOQL queries allow developers to retrieve one or more records from standard or custom objects based on specified criteria.

There are two primary ways to execute SOQL in Apex:

1. Inline (Static) SOQL: Queries are written directly in Apex code, enclosed in square brackets. For example:
List<Account> accts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];

2. Dynamic SOQL: Queries are constructed as strings at runtime and executed using the Database.query() method. For example:
String queryStr = 'SELECT Id, Name FROM Account WHERE Industry = :industryVar';
List<Account> accts = Database.query(queryStr);

Both approaches return sObject records that can then be processed in your Apex logic.

How SOQL Queries in Apex Work

Understanding how SOQL queries work in Apex requires knowledge of several core concepts:

1. Basic Query Syntax
A SOQL query follows this general structure:
SELECT fieldList FROM objectName [WHERE conditions] [ORDER BY field] [LIMIT number]

Key clauses include:
- SELECT: Specifies which fields to retrieve. You must explicitly list fields; there is no SELECT * in SOQL.
- FROM: Identifies the object (table) to query.
- WHERE: Filters records based on conditions. Supports operators like =, !=, LIKE, IN, NOT IN, <, >, <=, >=.
- ORDER BY: Sorts the results by a specified field in ASC (ascending) or DESC (descending) order.
- LIMIT: Restricts the number of records returned.
- OFFSET: Skips a specified number of records (useful for pagination).
- GROUP BY / HAVING: Used with aggregate functions for summarized queries.

2. Bind Variables
In inline SOQL, you can use Apex variables directly in your query by prefixing them with a colon (:). This is called a bind variable.
String industry = 'Technology';
List<Account> accts = [SELECT Id, Name FROM Account WHERE Industry = :industry];

Bind variables help prevent SOQL injection in inline queries and make queries dynamic without needing the Database.query() method.

3. Relationship Queries
SOQL supports querying related objects through relationship queries:

- Parent-to-Child (Sub-query): Retrieves child records related to a parent. The child relationship name (plural) is used in a nested SELECT.
List<Account> accts = [SELECT Id, Name, (SELECT Id, LastName FROM Contacts) FROM Account];

- Child-to-Parent (Dot Notation): Retrieves parent fields from a child object query.
List<Contact> cons = [SELECT Id, LastName, Account.Name FROM Contact];

For custom relationships, remember to use the __r suffix instead of __c when referencing the relationship name.

4. Return Types
SOQL queries in Apex can return:
- A List of sObjects: When you expect multiple records. This is the safest and most common approach.
List<Account> accts = [SELECT Id, Name FROM Account];

- A Single sObject: When you assign the result directly to a single sObject variable. This is risky because if the query returns zero records, it throws a QueryException (List has no rows for assignment to SObject). If it returns more than one record, it also throws an exception.
Account acct = [SELECT Id, Name FROM Account WHERE Id = :someId];

- An Integer (with COUNT()): When using aggregate queries.
Integer count = [SELECT COUNT() FROM Account WHERE Industry = 'Technology'];

- A List of AggregateResult: When using aggregate functions like SUM, AVG, MAX, MIN, COUNT(fieldName).
List<AggregateResult> results = [SELECT Industry, COUNT(Id) cnt FROM Account GROUP BY Industry];

5. Governor Limits
Because Salesforce is a multi-tenant platform, strict governor limits apply to SOQL queries:
- 100 SOQL queries per synchronous transaction.
- 200 SOQL queries per asynchronous transaction.
- 50,000 records can be retrieved by SOQL queries in a single transaction.
- Each query can return a maximum of 50,000 records.

Violating these limits causes a System.LimitException at runtime, which cannot be caught with try-catch blocks.

6. Avoiding SOQL Inside Loops (Bulkification)
One of the most critical best practices — and a frequent exam topic — is to never place SOQL queries inside loops. Doing so risks hitting the 100-query governor limit. Instead, query records before the loop and use collections (Maps, Sets, Lists) to organize and access data efficiently.

Bad Practice:
for (Contact c : contactList) {
  Account a = [SELECT Id, Name FROM Account WHERE Id = :c.AccountId]; // SOQL inside loop!
}

Best Practice:
Set<Id> accountIds = new Set<Id>();
for (Contact c : contactList) {
  accountIds.add(c.AccountId);
}
Map<Id, Account> accountMap = new Map<Id, Account>([SELECT Id, Name FROM Account WHERE Id IN :accountIds]);
for (Contact c : contactList) {
  Account a = accountMap.get(c.AccountId);
}

7. FOR UPDATE
You can lock records with FOR UPDATE to prevent other transactions from modifying them while your code is processing:
List<Account> accts = [SELECT Id, Name FROM Account WHERE Id = :someId FOR UPDATE];
This is useful for preventing race conditions but should be used carefully to avoid deadlocks.

8. SOQL For Loops
Apex provides a special for loop syntax that processes SOQL results in batches of 200 records, which helps avoid heap size limits when working with large data sets:
for (Account a : [SELECT Id, Name FROM Account]) {
  // Process each account
}

Or with batches of 200:
for (List<Account> accts : [SELECT Id, Name FROM Account]) {
  // accts contains up to 200 records per iteration
}

9. Null Handling and Empty Results
When a SOQL query returns no records:
- Assigning to a List returns an empty list (not null).
- Assigning to a single sObject throws a QueryException.
This is a very common exam question scenario.

10. Semi-Joins and Anti-Joins
SOQL supports semi-joins and anti-joins using IN and NOT IN with sub-queries:
List<Account> accts = [SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won')];

11. SOQL Injection Prevention
When using dynamic SOQL, user input can be exploited for SOQL injection. Use String.escapeSingleQuotes() to sanitize inputs, or use bind variables wherever possible. Inline SOQL with bind variables is inherently safe from SOQL injection.

12. Polymorphic Relationships (TYPEOF)
SOQL supports querying polymorphic fields (like WhoId or WhatId on Task/Event) using the TYPEOF expression in inline SOQL:
[SELECT Id, TYPEOF What WHEN Account THEN Name, Phone WHEN Opportunity THEN StageName END FROM Task]


Exam Tips: Answering Questions on SOQL Queries in Apex

Tip 1: Know the Governor Limits Cold
Memorize the key limits: 100 SOQL queries per synchronous transaction, 200 per asynchronous, and 50,000 total rows. Many exam questions present scenarios where a loop executes SOQL and ask what will happen — the answer is usually a governor limit exception.

Tip 2: Recognize SOQL-in-a-Loop Anti-Pattern
When you see SOQL inside a for loop in a code snippet, it is almost always the wrong answer or the source of the problem in the scenario. Look for the bulkified alternative that queries once and uses a Map or Set.

Tip 3: Understand Return Type Behavior
If a query that returns zero rows is assigned to a single sObject variable, it throws a QueryException. If assigned to a List, it returns an empty list. The exam frequently tests this distinction.

Tip 4: Know Relationship Query Syntax
Be comfortable with both parent-to-child (sub-queries with the child relationship name) and child-to-parent (dot notation) queries. For custom objects, remember __r is used for relationships, not __c. The exam often includes tricky syntax questions about this.

Tip 5: Understand Bind Variables
Know that bind variables (using the colon prefix) can reference local Apex variables, including Lists and Sets, which are useful with the IN operator. Bind variables work in inline SOQL. In dynamic SOQL, bind variable support is limited — they can reference simple variables in the same scope when using Database.query(), but more complex expressions may not work.

Tip 6: Dynamic vs. Static SOQL
Know when to use each. Static (inline) SOQL is preferred for its compile-time checking and safety from SOQL injection. Dynamic SOQL (Database.query()) is used when the query structure must change at runtime. The exam may ask which approach is appropriate for a given scenario.

Tip 7: Aggregate Queries Return AggregateResult
When using GROUP BY with functions like COUNT(Id), SUM(Amount), etc., the return type is List<AggregateResult>. Access fields using the get() method with the alias name. COUNT() without a field name returns an Integer directly.

Tip 8: SOQL For Loop for Large Data Sets
If the exam describes a scenario involving processing a large number of records (e.g., hundreds of thousands), the SOQL for loop is usually the best approach because it processes records in chunks and avoids heap size limits.

Tip 9: Watch for Fields Not Queried
If Apex code accesses a field that was not included in the SELECT clause, it throws a SObjectException. The Id field is always implicitly included, but all other fields must be explicitly queried. This is a common trap in exam code snippets.

Tip 10: Security Considerations
SOQL queries in Apex run in system mode by default, meaning they do not enforce the running user's field-level security or object permissions. If the question mentions enforcing user permissions, look for answers involving WITH SECURITY_ENFORCED, Security.stripInaccessible(), or WITH USER_MODE.

Tip 11: ORDER BY, LIMIT, and OFFSET
Know how to use these clauses together. LIMIT restricts the number of results. OFFSET skips a number of results. ORDER BY sorts results. The exam may ask about the correct order of these clauses: WHERE comes before ORDER BY, which comes before LIMIT, which comes before OFFSET.

Tip 12: Read the Code Carefully
Many exam questions show Apex code with SOQL queries and ask what happens when it runs. Read every line carefully. Check for SOQL in loops, missing fields in SELECT, incorrect relationship names, assignment to single sObject vs. List, and whether the query could return zero or multiple rows.

Summary

SOQL Queries in Apex form the backbone of data retrieval in Salesforce development. Mastering the syntax, understanding governor limits, recognizing anti-patterns like SOQL inside loops, knowing return type behavior, and understanding relationship queries are all critical for the Platform Developer 1 exam. Always look for bulkified, efficient patterns in exam questions, and remember that the safest approach is to assign SOQL results to a List rather than a single sObject. With these concepts firmly in mind, you will be well-prepared to tackle any SOQL-related question on the exam.