7.1 SOQL Query Fundamentals

7.1 SOQL Query Fundamentals

Quick Answer: SOQL (Salesforce Object Query Language) reads records from the database with a SQL-like shape: SELECT fields FROM object WHERE filters, optionally ORDER BY, LIMIT, and OFFSET. Relationship queries walk lookups/master-detail; aggregates summarize rows; bind variables, FOR UPDATE, and security modes (WITH SECURITY_ENFORCED, USER_MODE) appear constantly on Platform Developer I. Bulk-safe patterns—especially SOQL-for loops—protect you from governor limits.

This section is core Process Automation and Logic material. Exam items show short Apex snippets and ask which query compiles, which returns the right shape, or which pattern fails in a 200-record trigger. You must read SOQL the way a compiler and the query engine do—not the way casual SQL habits suggest.

SELECT, FROM, WHERE, ORDER BY, LIMIT, OFFSET

A basic inline query in Apex returns a typed list (or a single sObject when you expect one row):

List<Account> accounts = [
    SELECT Id, Name, Industry, AnnualRevenue
    FROM Account
    WHERE Industry = 'Technology'
    ORDER BY Name ASC
    LIMIT 100
    OFFSET 0
];

Single-row assignment throws a QueryException if zero rows or more than one row match:

Account a = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];

Using LIMIT 1 still fails if zero rows return when you assign to a single sObject. For optional results, query into a List and check isEmpty(), or use a loop.

Exam trap: SOQL is not full ANSI SQL. No arbitrary joins of unrelated objects, no SELECT * (you must list fields or use FIELDS() where allowed by API version and context), and custom fields/objects use __c / __r suffixes.

Relationship Queries: Child-to-Parent and Parent-to-Child

Child-to-parent (dot notation)

From a child, traverse the lookup or master-detail parent with dot notation and the relationship name:

List<Contact> contacts = [
    SELECT Id, LastName, Account.Name, Account.Owner.Name
    FROM Contact
    WHERE Account.Industry = 'Healthcare'
];
// Access: contacts[0].Account.Name

Custom lookups use the relationship name with __r (for example, Invoice__c.Account__r.Name when the field is Account__c).

Parent-to-child (subquery)

From a parent, embed a subquery using the child relationship name (often plural for standard objects):

List<Account> accounts = [
    SELECT Id, Name,
        (SELECT Id, LastName, Email FROM Contacts WHERE Email != null)
    FROM Account
    WHERE Name LIKE 'Acme%'
];
for (Account a : accounts) {
    for (Contact c : a.Contacts) {
        // process child rows already loaded
    }
}

Why exams love this: you avoid a second SOQL for children when the parent set is known—one query, nested lists. Limits still count subquery rows toward total retrieved rows. Relationship names must be exact; wrong child relationship names fail at compile/runtime depending on context.

Aggregate Queries

Aggregates return AggregateResult instances, not Account/Contact:

List<AggregateResult> results = [
    SELECT Industry industry, COUNT(Id) acctCount, SUM(AnnualRevenue) revenue
    FROM Account
    WHERE Industry != null
    GROUP BY Industry
    HAVING COUNT(Id) > 5
    ORDER BY COUNT(Id) DESC
];
for (AggregateResult ar : results) {
    String ind = (String)ar.get('industry');
    Integer cnt = (Integer)ar.get('acctCount');
    Decimal rev = (Decimal)ar.get('revenue');
}

Common functions: COUNT(), COUNT_DISTINCT(), SUM(), AVG(), MIN(), MAX(). Non-aggregated SELECT fields must appear in GROUP BY. HAVING filters groups after aggregation (like WHERE for groups).

Exam trap: Casting ar.get('alias') to the right type; aliases you define in SELECT are how you read values. Without an alias, keys follow platform naming rules—prefer explicit aliases in teaching and production code.

Bind Variables

Inject Apex values with colon bind syntax—prefer binds over string-built SOQL for safety and clarity:

String industry = 'Technology';
Set<Id> ownerIds = new Set<Id>{ UserInfo.getUserId() };
Decimal minRevenue = 100000;

List<Account> matches = [
    SELECT Id, Name
    FROM Account
    WHERE Industry = :industry
      AND OwnerId IN :ownerIds
      AND AnnualRevenue >= :minRevenue
];

Binds work with primitives, collections for IN, and sObject fields in many patterns. Dynamic SOQL (Database.query) also supports binds when you use the bind map / variable forms correctly—string concatenation of user input into SOQL is a classic anti-pattern (injection and hard-to-debug quoting).

FOR UPDATE and Concurrency

List<Account> locked = [
    SELECT Id, Name, Rating
    FROM Account
    WHERE Id IN :accountIds
    FOR UPDATE
];

FOR UPDATE locks the returned rows for the rest of the transaction so concurrent transactions wait rather than overwrite blindly. Use when you must read-modify-write without lost updates. Locks increase contention—do not lock large, unrelated sets “just in case.”

Security: WITH SECURITY_ENFORCED and USER_MODE

By default, Apex often runs in system mode (sharing may still apply depending on class keywords, but field- and object-level security are not automatically the same as the running user’s UI). Modern secure patterns:

// Fails the query if the user lacks FLS/object access to referenced fields/objects
List<Account> secure = [
    SELECT Id, Name, AnnualRevenue
    FROM Account
    WITH SECURITY_ENFORCED
    LIMIT 50
];

// Database.query with AccessLevel (API-version dependent patterns)
List<SObject> rows = Database.query(
    'SELECT Id, Name FROM Account LIMIT 10',
    AccessLevel.USER_MODE
);

WITH SECURITY_ENFORCED strips or fails based on field/object permissions rather than silently exposing restricted fields. USER_MODE (and related AccessLevel APIs) enforce user permissions on query and DML paths. Exam scenarios may contrast “works in system context but violates security best practices” versus enforcing user mode. Pair with WITH SECURITY_ENFORCED or USER_MODE when requirements say “respect the running user’s field access.”

Sharing is a separate axis: with sharing / without sharing / inherited sharing on the class control record visibility via sharing rules—not FLS. Do not confuse sharing keywords with SECURITY_ENFORCED.

Common Exam SOQL Errors

1. SOQL inside a loop over Trigger.new → blows the 100 SOQL queries governor quickly.
2. Missing fields in SELECT then reading a.Industry → null or runtime issues depending on path; always SELECT what you use.
3. Wrong relationship name (Contact vs Contacts in parent-to-child).
4. Assigning multi-row results to a single sObject without LIMIT and uniqueness guarantees.
5. Using SOSL-style free text in SOQL WHERE when structured fields exist—or vice versa (see 7.2).
6. AggregateResult treated as Account without casting get.
7. Ignoring null parents in child-to-parent paths (contact.Account may be null if lookup empty).

SOQL-for Loops vs Query-Then-Loop (Bulk)

Query-then-loop (fine for small, bounded sets)

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :ids];
for (Account a : accounts) {
    a.Description = 'Updated';
}
update accounts;

The entire result is loaded into the heap at once. Acceptable when the set is known small (for example, Trigger.new IDs already capped at 200).

SOQL-for loop (chunked retrieval)

for (Account a : [SELECT Id, Name FROM Account WHERE Industry = 'Energy']) {
    // Platform retrieves records in efficient chunks
}
// Or list chunks:
for (List<Account> batch : [SELECT Id, Name FROM Account WHERE Industry = 'Energy']) {
    // process batch; optional DML on batch carefully
}

SOQL-for loops stream records in batches so heap stays healthier on large queries. Prefer them when iterating large open-ended result sets. Still count toward total rows retrieved (50,000 synchronous row limit—see 7.4). Never issue a new SOQL per iteration of Trigger.new; collect IDs, query once (or use the for-loop form once), then process in memory.

Putting It Together for the Exam

When a question shows SOQL, ask:

1. Does it compile (fields, relationship names, aggregate shape)?
2. Is it bulk-safe (one query for the set, not per record)?
3. Does security/sharing requirement need SECURITY_ENFORCED, USER_MODE, or with sharing?
4. Is FOR UPDATE needed for a true race condition?
5. Should this be SOSL instead because the user typed free text across objects?

Master these patterns before triggers and async—those chapters assume you can load related data without wasting governors.