SOQL and SOSL in Salesforce

Part 39: SOQL and SOSL in Salesforce

Welcome back to the Salesforce series. In the previous installment we covered the fundamentals of Apex — variables, data types, control flow, and classes. All of that is useful, but a programming language inside a CRM is only as powerful as its ability to talk to the database. That is exactly what SOQL and SOSL do.

SOQL (Salesforce Object Query Language) lets you pull records from Salesforce objects the way SQL lets you pull rows from database tables. SOSL (Salesforce Object Search Language) lets you run full-text searches across multiple objects at once. Together they are the two query languages every Salesforce developer must know.

This is Part 39 of the series. We will cover SOQL syntax in depth, move into SOSL, compare the two, explore governor limits and indexing, learn how to use the Query Plan tool, and finish with a hands-on project. There is a lot of ground to cover, so let us get started.

What Is SOQL?

SOQL stands for Salesforce Object Query Language. It is the primary language for retrieving records from the Salesforce database. If you have ever written SQL, SOQL will feel familiar — but there are important differences.

SOQL vs SQL

When to Use SOQL

Use SOQL whenever you need to:

• Retrieve specific records by field values.
• Pull related records through parent-to-child or child-to-parent relationships.
• Perform aggregate calculations on data.
• Feed data into Apex logic for processing.
• Populate Lightning components or Visualforce pages.

SOQL is a read-only query language. You cannot use it to insert, update, or delete records. For that you use DML (Data Manipulation Language) statements in Apex, which we will cover in a future installment.

The SELECT Statement

Every SOQL query starts with SELECT. The basic structure is:

SELECT Field1, Field2, Field3
FROM ObjectName

Selecting Fields

You must list every field you want returned. There is no wildcard equivalent to SELECT *.

SELECT Id, Name, Industry, AnnualRevenue
FROM Account

This returns the Id, Name, Industry, and AnnualRevenue for every Account record.

Selecting the Id Field

The Id field is always available, but it is not automatically included in results unless you list it. If you run a query through the Developer Console Query Editor, the Id column appears regardless. In Apex, however, you only get what you ask for.

// This will throw an error if you try to access acc.Id
// without including Id in the query
List<Account> accounts = [SELECT Name FROM Account];
for (Account acc : accounts) {
    System.debug(acc.Name); // works
    // System.debug(acc.Id); // actually Id is auto-included, but other fields are not
}

Best practice: always include Id in your SELECT list.

Relationship Queries: Child-to-Parent

Salesforce objects are connected through lookup and master-detail relationships. When you query a child object and want data from its parent, you use dot notation.

SELECT Id, Name, Account.Name, Account.Industry
FROM Contact

Here, Contact is the child. Account is the parent. Account.Name traverses the relationship and pulls the Account’s Name into the Contact result.

For custom relationships, replace the __c suffix with __r:

SELECT Id, Name, Project__r.Name, Project__r.Status__c
FROM Task__c

You can traverse up to five levels of parent relationships:

SELECT Id, Contact.Account.Owner.Profile.Name
FROM Case

Relationship Queries: Parent-to-Child

When you query a parent object and want its related child records, you use a subquery with the child relationship name.

SELECT Id, Name,
    (SELECT Id, FirstName, LastName FROM Contacts)
FROM Account

The child relationship name for standard objects is usually the plural form of the child object name (e.g., Contacts, Cases, Opportunities). For custom objects, it ends in __r:

SELECT Id, Name,
    (SELECT Id, Name FROM Tasks__r)
FROM Project__c

Important: parent-to-child subqueries return a maximum of 200 child records per parent by default. If there are more, you need to use queryMore() in Apex.

Aggregate Functions

SOQL supports aggregate functions that let you summarize data:

COUNT

SELECT COUNT()
FROM Account
WHERE Industry = 'Technology'

In Apex, COUNT() returns an Integer, not a list:

Integer techCount = [SELECT COUNT() FROM Account WHERE Industry = 'Technology'];
System.debug('Technology accounts: ' + techCount);

SUM, AVG, MIN, MAX

These require a GROUP BY clause:

SELECT Industry, SUM(AnnualRevenue), AVG(AnnualRevenue), COUNT(Id)
FROM Account
GROUP BY Industry

In Apex, aggregate results are returned as List<AggregateResult>:

List<AggregateResult> results = [
    SELECT Industry, SUM(AnnualRevenue) totalRev, COUNT(Id) acctCount
    FROM Account
    GROUP BY Industry
];

for (AggregateResult ar : results) {
    System.debug(ar.get('Industry') + ': ' + ar.get('totalRev'));
}

Note the use of aliases (totalRev, acctCount). Without aliases, aggregate fields are accessed by their expression index (expr0, expr1), which makes code harder to read.

GROUP BY and HAVING

GROUP BY groups rows that share a value. HAVING filters those groups — it is the aggregate equivalent of WHERE.

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

This returns only those industries with more than 10 accounts.

The WHERE Clause

The WHERE clause filters which records are returned.

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

Comparison Operators

Logical Operators

Combine conditions with AND, OR, and NOT:

SELECT Id, Name
FROM Account
WHERE Industry = 'Technology'
AND AnnualRevenue > 1000000
AND (Rating = 'Hot' OR Rating = 'Warm')

Use parentheses to control evaluation order. Without parentheses, AND is evaluated before OR, which can produce unexpected results.

Date Literals

Salesforce provides built-in date literals that make date filtering simple and dynamic:

SELECT Id, Name, CreatedDate
FROM Opportunity
WHERE CreatedDate = LAST_N_DAYS:30

SELECT Id, Name, CloseDate
FROM Opportunity
WHERE CloseDate = THIS_QUARTER

Filtering on Null

To check for null or empty values, use = null or != null:

SELECT Id, Name
FROM Contact
WHERE Email != null

For text fields, an empty string and null are treated as the same thing in SOQL. Both = null and = '' will match empty text fields.

Filtering on Picklist Values

Picklist fields are queried as strings:

SELECT Id, Name
FROM Lead
WHERE Status = 'Open - Not Contacted'

For multi-select picklists, use INCLUDES and EXCLUDES:

SELECT Id, Name
FROM Lead
WHERE Interests__c INCLUDES ('Cloud Computing', 'AI')

INCLUDES returns records where the multi-select picklist contains any of the specified values.

The ORDER BY Clause

ORDER BY sorts the results.

SELECT Id, Name, AnnualRevenue
FROM Account
ORDER BY AnnualRevenue DESC

ASC and DESC

• ASC — ascending order (default).
• DESC — descending order.

NULLS FIRST and NULLS LAST

Control where null values appear in the sorted results:

SELECT Id, Name, AnnualRevenue
FROM Account
ORDER BY AnnualRevenue DESC NULLS LAST

By default, nulls sort first in ascending order and last in descending order. Use NULLS FIRST or NULLS LAST to override this.

Sorting by Multiple Fields

SELECT Id, Name, Industry, AnnualRevenue
FROM Account
ORDER BY Industry ASC, AnnualRevenue DESC

This sorts by Industry alphabetically, then within each industry sorts by AnnualRevenue from highest to lowest.

The LIMIT Clause

LIMIT restricts how many records are returned.

SELECT Id, Name
FROM Account
ORDER BY CreatedDate DESC
LIMIT 10

This returns the 10 most recently created accounts.

OFFSET for Pagination

OFFSET skips a specified number of rows. Combined with LIMIT, it enables pagination:

-- Page 1: first 10 records
SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 0

-- Page 2: records 11-20
SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 10

-- Page 3: records 21-30
SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 20

Important limits:

• The maximum OFFSET value is 2,000.
• For larger result sets, use queryMore() or a WHERE clause that filters based on the last retrieved record’s Id.

The IN Clause

The IN clause lets you filter against a list of values.

Filtering with a List

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

In Apex, you can bind a variable list:

List<String> industries = new List<String>{'Technology', 'Finance', 'Healthcare'};
List<Account> accounts = [
    SELECT Id, Name
    FROM Account
    WHERE Industry IN :industries
];

Semi-Joins (Filtering with Subqueries)

A semi-join returns records from the outer query where the value matches records in the inner query:

SELECT Id, Name
FROM Account
WHERE Id IN (
    SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Won'
)

This returns all Accounts that have at least one Closed Won Opportunity.

Anti-Joins

An anti-join returns records that do not match:

SELECT Id, Name
FROM Account
WHERE Id NOT IN (
    SELECT AccountId
    FROM Opportunity
)

This returns Accounts that have no Opportunities at all.

Subquery rules:

• The inner query can only return one field (usually an Id or lookup field).
• The inner query can have its own WHERE clause.
• You cannot nest subqueries more than one level deep.

Using SOQL in Apex

This is where SOQL becomes truly powerful. Apex gives you two ways to write queries: inline (static) and dynamic.

Inline SOQL

Inline queries are written directly inside square brackets. The Apex compiler validates them at compile time.

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

for (Account acc : accounts) {
    System.debug(acc.Name + ' - ' + acc.Industry);
}

Binding Variables

You can reference Apex variables inside inline queries using the colon (:) prefix:

String targetIndustry = 'Technology';
Decimal minRevenue = 1000000;

List<Account> accounts = [
    SELECT Id, Name, AnnualRevenue
    FROM Account
    WHERE Industry = :targetIndustry
    AND AnnualRevenue > :minRevenue
];

You can bind various types:

// Bind a Set of Ids
Set<Id> accountIds = new Set<Id>{'001xx000003DGbYAAW', '001xx000003DGbZAAW'};
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :accountIds];

// Bind a Date
Date cutoff = Date.today().addDays(-30);
List<Opportunity> recentOpps = [
    SELECT Id, Name, CloseDate
    FROM Opportunity
    WHERE CloseDate >= :cutoff
];

// Bind a list from another query
List<Contact> contacts = [SELECT AccountId FROM Contact WHERE Email != null];
Set<Id> acctIdsFromContacts = new Set<Id>();
for (Contact c : contacts) {
    acctIdsFromContacts.add(c.AccountId);
}
List<Account> relatedAccounts = [
    SELECT Id, Name FROM Account WHERE Id IN :acctIdsFromContacts
];

Querying into a Single Record

If you expect exactly one result, you can assign the query directly to an sObject variable instead of a list:

Account acc = [
    SELECT Id, Name
    FROM Account
    WHERE Name = 'Acme Corporation'
    LIMIT 1
];
System.debug(acc.Name);

Warning: If the query returns zero results, this throws a System.QueryException. Always use LIMIT 1 and consider wrapping in a try-catch or querying into a list first.

Querying Related Records

// Parent-to-child: get Accounts with their Contacts
List<Account> accounts = [
    SELECT Id, Name,
        (SELECT Id, FirstName, LastName, Email FROM Contacts)
    FROM Account
    WHERE Industry = 'Technology'
];

for (Account acc : accounts) {
    System.debug('Account: ' + acc.Name);
    for (Contact con : acc.Contacts) {
        System.debug('  Contact: ' + con.FirstName + ' ' + con.LastName);
    }
}

// Child-to-parent: get Contacts with Account info
List<Contact> contacts = [
    SELECT Id, FirstName, LastName, Account.Name, Account.Industry
    FROM Contact
    WHERE Account.Industry = 'Technology'
];

Dynamic SOQL

Dynamic SOQL lets you build query strings at runtime using Database.query():

String objectName = 'Account';
String fieldList = 'Id, Name, Industry';
String conditions = 'Industry = \'Technology\'';

String queryString = 'SELECT ' + fieldList + ' FROM ' + objectName;
if (String.isNotBlank(conditions)) {
    queryString += ' WHERE ' + conditions;
}

List<SObject> results = Database.query(queryString);
for (SObject record : results) {
    System.debug(record.get('Name'));
}

When to Use Dynamic SOQL

• When the object, fields, or conditions are not known until runtime.
• When building configurable, metadata-driven frameworks.
• When an admin-facing tool lets users define their own queries.

SOQL Injection Risk

Dynamic SOQL is vulnerable to injection attacks if you concatenate user input directly into the query string. Always use String.escapeSingleQuotes():

// DANGEROUS — do not do this
String userInput = 'Acme\' OR Name != \'';
String query = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';
// This would return all records

// SAFE — escape the input
String safeInput = String.escapeSingleQuotes(userInput);
String safeQuery = 'SELECT Id FROM Account WHERE Name = \'' + safeInput + '\'';

Even better, use bind variables with dynamic queries where possible. As of recent Apex releases, Database.queryWithBinds() supports binding a Map<String, Object>:

String query = 'SELECT Id, Name FROM Account WHERE Industry = :industry';
Map<String, Object> binds = new Map<String, Object>{
    'industry' => 'Technology'
};
List<Account> results = Database.queryWithBinds(query, binds, AccessLevel.USER_MODE);

This is the safest way to do dynamic SOQL.

Important SOQL Limits

Salesforce enforces governor limits on SOQL to protect the multi-tenant environment. Every developer must know these.

The 100 SOQL Query Limit

This is the limit developers hit most often. Each query inside a for loop counts as a separate query:

// BAD — this will hit the 100 query limit if there are > 100 contacts
List<Contact> contacts = [SELECT Id, AccountId FROM Contact LIMIT 200];
for (Contact c : contacts) {
    Account acc = [SELECT Id, Name FROM Account WHERE Id = :c.AccountId]; // query inside loop!
}

Fix it by bulkifying — query all the data you need in one shot:

// GOOD — one query for contacts, one query for accounts
List<Contact> contacts = [SELECT Id, AccountId FROM Contact LIMIT 200];
Set<Id> accountIds = new Set<Id>();
for (Contact c : contacts) {
    accountIds.add(c.AccountId);
}
Map<Id, Account> accountMap = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);
for (Contact c : contacts) {
    Account acc = accountMap.get(c.AccountId);
    if (acc != null) {
        System.debug(c.Id + ' belongs to ' + acc.Name);
    }
}

The 50,000 Row Limit

If your query would return more than 50,000 rows in a single transaction, Salesforce throws a System.LimitException. Strategies to avoid this:

• Add selective WHERE filters.
• Use LIMIT to cap the results.
• Process data in batches using Batch Apex.
• Use the FOR loop form of SOQL, which processes records in chunks of 200:

for (List<Account> batch : [SELECT Id, Name FROM Account]) {
    // 'batch' contains up to 200 records at a time
    // Salesforce manages the chunking automatically
    System.debug('Processing batch of ' + batch.size() + ' accounts');
}

queryMore

When working through the API (REST or SOAP), if a query returns more than 2,000 records, Salesforce returns a query locator. You call queryMore() with that locator to retrieve the next batch. In Apex, the for loop approach shown above handles this automatically.

What Is SOSL?

SOSL stands for Salesforce Object Search Language. While SOQL queries specific objects and fields, SOSL performs full-text searches across multiple objects simultaneously. It is built on top of the Salesforce search index.

When to Use SOSL

• When you want to search for a term across many objects at once.
• When you are building a search feature for users.
• When you do not know which object the data lives in.
• When searching text-heavy fields (like descriptions, comments, or email bodies).

Think of SOSL as the query behind the Global Search bar in Salesforce.

The FIND Clause

Every SOSL query starts with FIND. The syntax is:

FIND {searchTerm}

The search term goes inside curly braces.

FIND {Acme}

This searches for “Acme” across all searchable objects and fields.

Wildcards

• * — matches zero or more characters at the middle or end of a term. Example: FIND {Acm*} matches “Acme”, “Acmon”, “Acm”.
• ? — matches exactly one character. Example: FIND {A?me} matches “Acme”, “Aime”, “Axme”.

You cannot use a wildcard as the first character of a search term.

Logical Operators in Search Terms

FIND {Acme AND Corporation}    -- both terms must be present
FIND {Acme OR Global}          -- either term
FIND {Acme AND NOT Inc}        -- Acme but not Inc
FIND {"Acme Corporation"}      -- exact phrase

Escape Characters

Reserved characters (? & | ! { } [ ] ( ) ^ ~ * : \ " ' + -) need to be escaped with a backslash:

FIND {Acme \& Partners}

The RETURNING Clause

By default, SOSL searches all searchable objects. The RETURNING clause narrows which objects and fields are returned.

FIND {Acme}
RETURNING Account(Id, Name, Industry), Contact(Id, FirstName, LastName, Email)

Filtering Within RETURNING

You can add WHERE, ORDER BY, and LIMIT inside each RETURNING block:

FIND {Cloud}
RETURNING
    Account(Id, Name WHERE Industry = 'Technology' ORDER BY Name LIMIT 10),
    Contact(Id, FirstName, LastName WHERE MailingState = 'CA' LIMIT 5)

This gives you fine-grained control over what comes back from each object.

The IN Clause (SOSL)

The IN clause in SOSL specifies which types of fields to search:

FIND {john@example.com}
IN EMAIL FIELDS
RETURNING Contact(Id, FirstName, LastName, Email)

FIND {415-555}
IN PHONE FIELDS
RETURNING Contact(Id, Name, Phone), Lead(Id, Name, Phone)

Important SOSL Limits

The Search Index Lag

This is the most important thing to understand about SOSL. Unlike SOQL, which queries the database directly, SOSL queries the search index. After a record is created or updated, it can take several seconds to a few minutes before the search index is updated. This means:

• SOSL is not appropriate for querying records that were just created in the same transaction.
• In test classes, you must use Test.setFixedSearchResults() to control what SOSL returns, because test data is not indexed.

@isTest
static void testSOSL() {
    Account testAcct = new Account(Name = 'Test Acme Corp');
    insert testAcct;

    // Tell the test framework what SOSL should return
    Test.setFixedSearchResults(new List<Id>{testAcct.Id});

    List<List<SObject>> searchResults = [
        FIND 'Acme' IN ALL FIELDS
        RETURNING Account(Id, Name)
    ];

    List<Account> accounts = (List<Account>) searchResults[0];
    System.assertEquals(1, accounts.size());
}

When to Use SOQL vs SOSL

This is one of the most commonly asked questions on the Salesforce Developer certification exams. Here is a comparison:

Rule of thumb: If you know which object and fields to query, use SOQL. If you are searching for text across multiple objects or do not know where the data lives, use SOSL.

Using the Developer Console for Ad Hoc Queries

The Developer Console has a built-in Query Editor that is useful for running quick SOQL and SOSL queries without writing Apex.

How to Open the Query Editor

1. Click the gear icon in the top-right corner of Salesforce and select Developer Console.
2. At the bottom of the Developer Console, you will see the Query Editor tab.
3. Type your SOQL or SOSL query in the text area.
4. Click Execute.

Tips for the Query Editor

• Results appear in a table below the query. You can click column headers to sort.
• The Query Editor has a maximum of 2,000 rows returned.
• You can check the Use Tooling API checkbox to query metadata objects like ApexClass, ApexTrigger, and CustomField.
• For SOSL queries, wrap the query in the standard SOSL syntax (no square brackets needed).

-- SOQL in Query Editor
SELECT Id, Name, Industry FROM Account WHERE Industry = 'Technology' LIMIT 50

-- SOSL in Query Editor
FIND {Acme} RETURNING Account(Id, Name), Contact(Id, Name)

Using an IDE for Ad Hoc Queries

While the Developer Console works, most developers prefer using Visual Studio Code with the Salesforce Extensions.

The SOQL Builder Extension

The Salesforce Extension Pack for VS Code includes a SOQL Builder that provides a graphical interface for building SOQL queries. You can:

1. Open the command palette (Cmd+Shift+P or Ctrl+Shift+P).
2. Search for SFDX: Create Query in SOQL Builder.
3. Select your object, fields, and conditions visually.
4. Run the query and see results in a table.

Running Queries in Anonymous Apex

You can also run SOQL and SOSL from the Execute Anonymous Apex window:

1. Open the command palette.
2. Search for SFDX: Execute Anonymous Apex with Currently Selected Text.
3. Write your Apex code with embedded SOQL:

List<Account> accounts = [SELECT Id, Name FROM Account LIMIT 10];
for (Account a : accounts) {
    System.debug(a.Name);
}

4. Select the code and run it. Output appears in the Output panel.

The SOQL Query Extension

The Salesforce SOQL extension lets you create .soql files in your project. You write raw SOQL in the file, and the extension provides syntax highlighting, autocomplete, and the ability to run the query directly from the editor.

What Is Query Indexing?

Behind the scenes, Salesforce maintains indexes on certain fields to speed up queries. Understanding indexing is critical for writing performant queries, especially in orgs with millions of records.

Standard Indexes

Salesforce automatically indexes these fields on every object:

• Id
• Name
• CreatedDate
• LastModifiedDate
• SystemModstamp
• RecordType
• OwnerId
• Lookup and master-detail relationship fields
• Audit fields (CreatedById, LastModifiedById)
• External ID fields (custom fields marked as External ID are automatically indexed)

Custom Indexes

For custom fields that are not auto-indexed, you can request a custom index by contacting Salesforce Support. Custom indexes are appropriate when:

• A custom field is frequently used in WHERE clauses.
• The field has high selectivity (it filters out most records).
• Queries on the field are slow due to large data volumes.

Selective Queries

A query is considered selective if it uses an indexed field in the WHERE clause and the filter narrows the results to a small percentage of total records. Salesforce’s query optimizer decides whether to use an index based on these thresholds:

• Standard index: The filter must return less than 30% of the first million records and less than 15% of records beyond the first million.
• Custom index: The filter must return less than 10% of the first million records and less than 5% of records beyond the first million.

If a query is not selective, Salesforce performs a full table scan, which is slow on large objects.

Making Queries Selective

-- Non-selective: boolean fields are not indexed and have low cardinality
SELECT Id, Name FROM Account WHERE IsActive__c = true

-- Selective: uses the indexed Name field
SELECT Id, Name FROM Account WHERE Name = 'Acme Corporation'

-- Selective: uses a lookup field (auto-indexed)
SELECT Id, Name FROM Contact WHERE AccountId = '001xx000003DGbY'

-- Selective: combines an indexed field with a non-indexed field
-- The optimizer can still use the index on AccountId
SELECT Id, Name FROM Contact WHERE AccountId = '001xx000003DGbY' AND Department = 'Sales'

Using the Developer Console Query Plan Tool

The Query Plan tool in the Developer Console shows you how Salesforce plans to execute a query. It tells you whether an index will be used, the estimated cost, and how selective the query is.

Enabling the Query Plan Tool

The Query Plan tool is not visible by default. To enable it:

1. Open the Developer Console.
2. Click Help > Preferences.
3. Check Enable Query Plan.
4. Click Save.

After enabling it, a Query Plan button appears next to the Execute button in the Query Editor.

Reading the Query Plan Output

When you click Query Plan, Salesforce returns a table with these columns:

Interpreting the Results

• Relative Cost < 1.0: The query is selective. The optimizer will likely use an index. Good.
• Relative Cost > 1.0: The query is not selective. A table scan is likely. This can be slow on large objects.

Example

SELECT Id, Name FROM Account WHERE Name = 'Acme Corporation'

Query Plan output might show:

The relative cost of 0.00 means this is highly selective. The optimizer will use the Name index.

Compare that to:

SELECT Id, Name FROM Account WHERE Description LIKE '%cloud%'

A cost of 1.33 with a TableScan means this query will scan every record. It will be slow on large data sets.

Tips for Cost Optimization

1. Always filter on indexed fields when querying large objects.
2. Avoid LIKE with leading wildcards (%term) — they cannot use indexes.
3. Avoid NOT IN and negative operators — they tend to reduce selectivity.
4. Combine indexed and non-indexed filters — the optimizer can use the index on the indexed field first, then apply the non-indexed filter to the smaller result set.
5. Request custom indexes for frequently queried custom fields in large orgs.

PROJECT: Querying the Database in an Apex Class

Let us put everything together. We will build a service class called AccountQueryService that demonstrates various SOQL and SOSL techniques.

Step 1: Create the Service Class

Open VS Code, create a new Apex class, and name it AccountQueryService.

public with sharing class AccountQueryService {

    /**
     * Basic SOQL: get accounts by industry
     */
    public static List<Account> getAccountsByIndustry(String industry) {
        return [
            SELECT Id, Name, Industry, AnnualRevenue
            FROM Account
            WHERE Industry = :industry
            ORDER BY Name ASC
        ];
    }

    /**
     * Relationship query: get accounts with their contacts
     */
    public static List<Account> getAccountsWithContacts(String industry) {
        return [
            SELECT Id, Name,
                (SELECT Id, FirstName, LastName, Email
                 FROM Contacts
                 ORDER BY LastName ASC)
            FROM Account
            WHERE Industry = :industry
        ];
    }

    /**
     * Aggregate query: get revenue summary by industry
     */
    public static List<AggregateResult> getRevenueSummaryByIndustry() {
        return [
            SELECT Industry,
                   COUNT(Id) acctCount,
                   SUM(AnnualRevenue) totalRevenue,
                   AVG(AnnualRevenue) avgRevenue,
                   MAX(AnnualRevenue) maxRevenue
            FROM Account
            WHERE Industry != null
            GROUP BY Industry
            HAVING COUNT(Id) > 5
            ORDER BY SUM(AnnualRevenue) DESC
        ];
    }

    /**
     * Pagination: get a page of accounts
     */
    public static List<Account> getAccountPage(Integer pageNumber, Integer pageSize) {
        Integer offset = (pageNumber - 1) * pageSize;
        if (offset > 2000) {
            throw new QueryException('OFFSET cannot exceed 2000. Use a keyset approach for deeper pagination.');
        }
        return [
            SELECT Id, Name, Industry
            FROM Account
            ORDER BY Name ASC
            LIMIT :pageSize
            OFFSET :offset
        ];
    }

    /**
     * Semi-join: accounts that have closed-won opportunities
     */
    public static List<Account> getAccountsWithClosedWonOpps() {
        return [
            SELECT Id, Name
            FROM Account
            WHERE Id IN (
                SELECT AccountId
                FROM Opportunity
                WHERE StageName = 'Closed Won'
            )
            ORDER BY Name
        ];
    }

    /**
     * Anti-join: accounts with no opportunities
     */
    public static List<Account> getAccountsWithNoOpps() {
        return [
            SELECT Id, Name
            FROM Account
            WHERE Id NOT IN (
                SELECT AccountId
                FROM Opportunity
                WHERE AccountId != null
            )
            ORDER BY Name
        ];
    }

    /**
     * Dynamic SOQL: build a query based on user-provided filters
     */
    public static List<Account> searchAccounts(
        String nameFilter,
        String industry,
        Decimal minRevenue,
        Integer maxResults
    ) {
        String query = 'SELECT Id, Name, Industry, AnnualRevenue FROM Account';
        List<String> conditions = new List<String>();

        if (String.isNotBlank(nameFilter)) {
            String safeName = String.escapeSingleQuotes(nameFilter);
            conditions.add('Name LIKE \'%' + safeName + '%\'');
        }
        if (String.isNotBlank(industry)) {
            String safeIndustry = String.escapeSingleQuotes(industry);
            conditions.add('Industry = \'' + safeIndustry + '\'');
        }
        if (minRevenue != null) {
            conditions.add('AnnualRevenue >= ' + minRevenue);
        }

        if (!conditions.isEmpty()) {
            query += ' WHERE ' + String.join(conditions, ' AND ');
        }

        query += ' ORDER BY Name ASC';

        if (maxResults != null && maxResults > 0) {
            query += ' LIMIT ' + maxResults;
        }

        return Database.query(query);
    }

    /**
     * SOSL: search for a term across Accounts, Contacts, and Leads
     */
    public static Map<String, List<SObject>> globalSearch(String searchTerm) {
        if (String.isBlank(searchTerm) || searchTerm.length() < 2) {
            throw new QueryException('Search term must be at least 2 characters.');
        }

        String sanitized = String.escapeSingleQuotes(searchTerm);

        List<List<SObject>> searchResults = [
            FIND :sanitized IN ALL FIELDS
            RETURNING
                Account(Id, Name, Industry ORDER BY Name LIMIT 20),
                Contact(Id, FirstName, LastName, Email ORDER BY LastName LIMIT 20),
                Lead(Id, Name, Company, Email ORDER BY Name LIMIT 20)
        ];

        Map<String, List<SObject>> resultMap = new Map<String, List<SObject>>();
        resultMap.put('Accounts', searchResults[0]);
        resultMap.put('Contacts', searchResults[1]);
        resultMap.put('Leads', searchResults[2]);

        return resultMap;
    }

    /**
     * Demonstrate for-loop SOQL for large data processing
     */
    public static Integer countHighValueAccounts(Decimal revenueThreshold) {
        Integer count = 0;
        for (List<Account> batch : [
            SELECT Id, AnnualRevenue
            FROM Account
            WHERE AnnualRevenue >= :revenueThreshold
        ]) {
            count += batch.size();
        }
        return count;
    }

    /**
     * Using Database.queryWithBinds for safe dynamic queries
     */
    public static List<Account> safeSearch(String industry, Decimal minRevenue) {
        String query = 'SELECT Id, Name, Industry, AnnualRevenue FROM Account';
        query += ' WHERE Industry = :industry AND AnnualRevenue >= :minRevenue';
        query += ' ORDER BY AnnualRevenue DESC LIMIT 50';

        Map<String, Object> binds = new Map<String, Object>{
            'industry' => industry,
            'minRevenue' => minRevenue
        };

        return Database.queryWithBinds(query, binds, AccessLevel.USER_MODE);
    }

    public class QueryException extends Exception {}
}

Step 2: Create a Test Class

Every production Apex class needs tests. Create AccountQueryServiceTest:

@isTest
private class AccountQueryServiceTest {

    @TestSetup
    static void createTestData() {
        List<Account> accounts = new List<Account>();
        for (Integer i = 0; i < 10; i++) {
            accounts.add(new Account(
                Name = 'Tech Account ' + i,
                Industry = 'Technology',
                AnnualRevenue = 1000000 + (i * 100000)
            ));
        }
        accounts.add(new Account(
            Name = 'Finance Account',
            Industry = 'Finance',
            AnnualRevenue = 5000000
        ));
        insert accounts;

        // Create contacts for the first tech account
        Account firstAcct = [SELECT Id FROM Account WHERE Name = 'Tech Account 0' LIMIT 1];
        List<Contact> contacts = new List<Contact>();
        for (Integer i = 0; i < 3; i++) {
            contacts.add(new Contact(
                FirstName = 'Test',
                LastName = 'Contact ' + i,
                AccountId = firstAcct.Id,
                Email = 'test' + i + '@example.com'
            ));
        }
        insert contacts;

        // Create an opportunity
        insert new Opportunity(
            Name = 'Big Deal',
            AccountId = firstAcct.Id,
            StageName = 'Closed Won',
            CloseDate = Date.today()
        );
    }

    @isTest
    static void testGetAccountsByIndustry() {
        List<Account> results = AccountQueryService.getAccountsByIndustry('Technology');
        System.assertEquals(10, results.size(), 'Should return 10 technology accounts');
    }

    @isTest
    static void testGetAccountsWithContacts() {
        List<Account> results = AccountQueryService.getAccountsWithContacts('Technology');
        Boolean foundContacts = false;
        for (Account acc : results) {
            if (acc.Contacts != null && !acc.Contacts.isEmpty()) {
                foundContacts = true;
            }
        }
        System.assert(foundContacts, 'At least one account should have contacts');
    }

    @isTest
    static void testGetRevenueSummaryByIndustry() {
        List<AggregateResult> results = AccountQueryService.getRevenueSummaryByIndustry();
        System.assert(results.size() > 0, 'Should return at least one industry group');
    }

    @isTest
    static void testGetAccountPage() {
        List<Account> page1 = AccountQueryService.getAccountPage(1, 5);
        System.assertEquals(5, page1.size(), 'Page 1 should return 5 accounts');

        List<Account> page2 = AccountQueryService.getAccountPage(2, 5);
        System.assert(page2.size() > 0, 'Page 2 should return accounts');
    }

    @isTest
    static void testGetAccountsWithClosedWonOpps() {
        List<Account> results = AccountQueryService.getAccountsWithClosedWonOpps();
        System.assertEquals(1, results.size(), 'Only one account has a Closed Won opp');
    }

    @isTest
    static void testGetAccountsWithNoOpps() {
        List<Account> results = AccountQueryService.getAccountsWithNoOpps();
        System.assert(results.size() >= 10, 'Most test accounts have no opps');
    }

    @isTest
    static void testSearchAccounts() {
        List<Account> results = AccountQueryService.searchAccounts(
            'Tech', 'Technology', 1000000, 5
        );
        System.assert(results.size() <= 5, 'Should respect LIMIT');
    }

    @isTest
    static void testGlobalSearch() {
        // Set fixed search results for SOSL in tests
        Account testAcct = [SELECT Id FROM Account WHERE Name = 'Tech Account 0' LIMIT 1];
        Test.setFixedSearchResults(new List<Id>{testAcct.Id});

        Map<String, List<SObject>> results = AccountQueryService.globalSearch('Tech');
        System.assert(results.containsKey('Accounts'), 'Should have Accounts key');
    }

    @isTest
    static void testGlobalSearchMinLength() {
        try {
            AccountQueryService.globalSearch('A');
            System.assert(false, 'Should have thrown an exception');
        } catch (AccountQueryService.QueryException e) {
            System.assert(e.getMessage().contains('at least 2 characters'));
        }
    }

    @isTest
    static void testCountHighValueAccounts() {
        Integer count = AccountQueryService.countHighValueAccounts(1500000);
        System.assert(count > 0, 'Should find high-value accounts');
    }

    @isTest
    static void testSafeSearch() {
        List<Account> results = AccountQueryService.safeSearch('Technology', 1000000);
        System.assert(results.size() > 0, 'Should return technology accounts above revenue threshold');
    }
}

Step 3: Deploy and Run

1. Save both files.
2. Deploy to your Salesforce org: sfdx force:source:deploy -p force-app/main/default/classes.
3. Run the tests: sfdx force:apex:test:run --classnames AccountQueryServiceTest --resultformat human.
4. Verify all tests pass.

What This Project Covers

This single service class demonstrates:

• Inline SOQL with bind variables
• Parent-to-child relationship subqueries
• Aggregate queries with GROUP BY and HAVING
• Pagination with LIMIT and OFFSET
• Semi-joins and anti-joins
• Dynamic SOQL with proper escaping
• Database.queryWithBinds for safe dynamic queries
• SOSL with RETURNING and multiple objects
• For-loop SOQL for batch processing
• Proper test coverage including SOSL test setup

Summary

SOQL and SOSL are the two query languages that let you read data from the Salesforce database. SOQL is precise, real-time, and works against specific objects and fields. SOSL is broad, index-based, and searches across multiple objects at once. Every Salesforce developer needs to know when to use which.

Key takeaways:

• SOQL uses SELECT ... FROM ... WHERE syntax, similar to SQL but without SELECT * or JOIN.
• Relationship queries let you traverse parent-to-child and child-to-parent connections using dot notation and subqueries.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) work with GROUP BY and HAVING.
• Bind variables in Apex let you safely pass values into inline SOQL.
• Dynamic SOQL via Database.query() and Database.queryWithBinds() is powerful but requires careful handling to prevent injection.
• Governor limits — 100 SOQL queries and 50,000 rows per transaction — are hard constraints you must design around.
• SOSL uses FIND ... RETURNING syntax for full-text search with a slight index lag.
• Indexing and selectivity determine whether queries use fast index lookups or slow table scans.
• The Query Plan tool in the Developer Console shows you the estimated cost and execution strategy for any query.

Master these concepts and you will be able to build efficient, scalable data operations in any Salesforce application.

In Part 40, we will cover Exceptions in Apex — how to throw, catch, and handle errors gracefully, including custom exception classes, try-catch-finally blocks, and strategies for building resilient Apex code. See you there.