SOQL & SOSL Deep Dive

0/9 in this phase0/41 across the roadmap

📖 Concept

SOQL (Salesforce Object Query Language) and SOSL (Salesforce Object Search Language) are the two ways to retrieve data in Apex. Understanding their differences, performance characteristics, and optimization techniques is critical for every Salesforce developer.

SOQL vs SOSL:

SOQL:
- Queries ONE object at a time (with relationships)
- Structured query: SELECT fields FROM object WHERE conditions
- Returns exact matches
- Can traverse relationships (parent-to-child, child-to-parent)
- 100 queries per synchronous transaction / 200 async
- Up to 50,000 rows returned

SOSL:
- Searches ACROSS MULTIPLE objects simultaneously
- Text-based search: FIND 'search term' IN ALL FIELDS
- Returns approximate matches (search index)
- Searches across all text-searchable fields
- 20 searches per transaction
- Up to 2,000 rows returned

SOQL essentials:

WHERE clauses — filter records (=, !=, >, <, IN, LIKE, NOT IN)
ORDER BY — sort results (ASC/DESC)
LIMIT / OFFSET — pagination
GROUP BY / HAVING — aggregate queries (COUNT, SUM, AVG, MIN, MAX)
Relationship queries — traverse parent (dot notation) and child (subquery)
Date literals — TODAY, LAST_N_DAYS:30, THIS_QUARTER, etc.
Polymorphic queries — TYPEOF for querying relationships that can reference multiple objects

Query optimization: Salesforce maintains custom indexes on certain fields. Your WHERE clause must be selective — meaning it uses an indexed field that filters out at least 90% of records. Non-selective queries on large objects (>100K records) will fail.

Indexed fields (automatic):

Id, Name, OwnerId, CreatedDate, SystemModstamp
Lookup and Master-Detail relationship fields
Custom fields marked as "External ID" or "Unique"

SOQL anti-patterns to avoid:

Queries inside loops (governor limit violation)
SELECT * equivalent (SELECT all fields — wastes heap and query time)
Non-selective queries on large objects
Not using bind variables (SOQL injection risk)

💻 Code Example

codeTap to expand ⛶

1// SOQL & SOSL Mastery
2
3public class QueryExamples {
4    
5    // 1. Basic SOQL with filters and ordering
6    public static List<Account> getActiveAccounts() {
7        return [
8            SELECT Id, Name, Industry, AnnualRevenue, CreatedDate
9            FROM Account
10            WHERE Industry = 'Technology'
11            AND AnnualRevenue > 1000000
12            AND CreatedDate = THIS_YEAR
13            ORDER BY AnnualRevenue DESC
14            LIMIT 50
15        ];
16    }
17    
18    // 2. Relationship queries — Parent-to-Child (subquery)
19    public static List<Account> getAccountsWithContacts() {
20        return [
21            SELECT Name, Industry,
22                (SELECT FirstName, LastName, Email, Title
23                 FROM Contacts
24                 WHERE Email != null
25                 ORDER BY LastName ASC
26                 LIMIT 5)
27            FROM Account
28            WHERE Industry = 'Technology'
29            LIMIT 10
30        ];
31        // Access child records:
32        // for (Account acc : results) {
33        //     for (Contact c : acc.Contacts) { ... }
34        // }
35    }
36    
37    // 3. Relationship queries — Child-to-Parent (dot notation)
38    public static List<Contact> getContactsWithAccountInfo() {
39        return [
40            SELECT FirstName, LastName, 
41                Account.Name,          // Parent field
42                Account.Industry,      // Navigate up
43                Account.Owner.Name     // Multiple levels up
44            FROM Contact
45            WHERE Account.Industry = 'Technology'
46        ];
47    }
48    
49    // 4. Aggregate queries
50    public static void aggregateExamples() {
51        // COUNT
52        Integer totalAccounts = [SELECT COUNT() FROM Account];
53        
54        // GROUP BY with aggregates
55        List<AggregateResult> byIndustry = [
56            SELECT Industry, COUNT(Id) cnt, SUM(AnnualRevenue) totalRev
57            FROM Account
58            WHERE Industry != null
59            GROUP BY Industry
60            HAVING COUNT(Id) > 5
61            ORDER BY COUNT(Id) DESC
62        ];
63        
64        for (AggregateResult ar : byIndustry) {
65            String industry = (String) ar.get('Industry');
66            Integer count = (Integer) ar.get('cnt');
67            Decimal revenue = (Decimal) ar.get('totalRev');
68            System.debug(industry + ': ' + count + ' accounts, $' + revenue);
69        }
70    }
71    
72    // 5. Dynamic SOQL (useful for configurable queries)
73    public static List<SObject> dynamicQuery(
74        String objectName,
75        List<String> fields,
76        String whereClause,
77        Integer limitCount
78    ) {
79        String query = 'SELECT ' + String.join(fields, ', ') +
80            ' FROM ' + String.escapeSingleQuotes(objectName);
81        
82        if (String.isNotBlank(whereClause)) {
83            query += ' WHERE ' + whereClause;
84        }
85        
86        query += ' LIMIT ' + limitCount;
87        
88        return Database.query(query);
89    }
90    
91    // 6. SOSL — Search across multiple objects
92    public static void searchExamples() {
93        // SOSL searches across objects
94        List<List<SObject>> results = [
95            FIND 'Acme*' IN ALL FIELDS
96            RETURNING 
97                Account(Id, Name, Industry WHERE Industry = 'Technology'),
98                Contact(Id, FirstName, LastName, Email),
99                Opportunity(Id, Name, Amount, StageName)
100            LIMIT 20
101        ];
102        
103        List<Account> accounts = (List<Account>) results[0];
104        List<Contact> contacts = (List<Contact>) results[1];
105        List<Opportunity> opps = (List<Opportunity>) results[2];
106        
107        System.debug('Found ' + accounts.size() + ' accounts');
108        System.debug('Found ' + contacts.size() + ' contacts');
109    }
110    
111    // 7. Date literals and filtering
112    public static List<Opportunity> getRecentOpportunities() {
113        return [
114            SELECT Name, StageName, CloseDate, Amount
115            FROM Opportunity
116            WHERE CloseDate = THIS_QUARTER
117            AND StageName NOT IN ('Closed Won', 'Closed Lost')
118            AND Amount > 50000
119            ORDER BY CloseDate ASC
120        ];
121    }
122    
123    // 8. Bind variables (prevent SOQL injection)
124    public static List<Account> safeQuery(String searchName) {
125        // GOOD — bind variable (safe from injection)
126        String safeName = '%' + searchName + '%';
127        return [SELECT Id, Name FROM Account WHERE Name LIKE :safeName];
128        
129        // BAD — string concatenation (SOQL injection vulnerable!)
130        // String query = 'SELECT Id FROM Account WHERE Name LIKE '%'
131        //     + searchName + '%'';
132        // return Database.query(query);
133    }
134    
135    // 9. Query performance — using selective filters
136    public static List<Account> performantQuery() {
137        // SELECTIVE — uses indexed field (Id, Name, lookups, External IDs)
138        return [
139            SELECT Id, Name FROM Account 
140            WHERE Name = 'Acme Corporation'  // Name is indexed
141            LIMIT 1
142        ];
143        
144        // NON-SELECTIVE — scans all records (fails on large objects)
145        // SELECT Id FROM Account WHERE Description LIKE '%enterprise%'
146        // Description is NOT indexed — full table scan!
147    }
148    
149    // 10. FOR UPDATE — record locking
150    public static void lockRecords() {
151        List<Account> accounts = [
152            SELECT Id, Name, AnnualRevenue
153            FROM Account
154            WHERE Id = :accountId
155            FOR UPDATE  // Locks the record to prevent concurrent modification
156        ];
157        // Other transactions trying to update this record will wait
158        accounts[0].AnnualRevenue += 50000;
159        update accounts;
160    }
161}

🏋️ Practice Exercise

SOQL/SOSL Mastery Exercises:

Write a SOQL query that returns Accounts with their Opportunities and Contacts in a single query
Write an aggregate query that shows total Opportunity Amount by Stage, grouped by Account Industry
Use SOSL to search for 'John' across Account, Contact, and Lead objects simultaneously
Write a dynamic SOQL builder that accepts a Map of field→value filters and constructs a WHERE clause
Query all Contacts where the Account was created in the last 30 days (child-to-parent with date literal)
Write a SOQL query that uses OFFSET for pagination (page 3, 25 records per page)
Use FOR UPDATE in a query and explain when locking is necessary
Write a query that finds all Accounts with NO related Contacts (anti-join pattern)
Build a reusable query class that handles pagination, sorting, and filtering dynamically
Use the Query Plan tool in Developer Console to analyze query selectivity for 5 different queries

⚠️ Common Mistakes

Writing SOQL inside loops — this is the #1 cause of governor limit failures. Always query once, build a Map, then iterate
Not using bind variables in dynamic SOQL — String.escapeSingleQuotes helps but bind variables are the proper protection against SOQL injection
Selecting all fields (SELECT *) — Apex doesn't support *, but developers query too many fields, wasting heap. Query only what you need
Using LIKE with leading wildcards on large objects — '%searchterm%' prevents index usage and causes non-selective query errors
Not understanding SOQL vs SOSL — SOQL is for structured queries on known objects; SOSL is for text search across multiple objects

💼 Interview Questions

🎤 Mock Interview

Mock interview is powered by AI for SOQL & SOSL Deep Dive. Login to unlock this feature.

Was this topic helpful?

← PreviousApex Syntax, Data Types & Collections Next →DML Operations & Database Methods