SOQL and SOSL Interview Questions and Answers

1What it is

SOQL queries one object (and its related records) with exact filters - like SQL's SELECT. SOSL searches text across many objects at once using the search index.

2Why it matters

They answer different questions. When you know where the data is, SOQL is precise. When a user types "acme" and it could be an account, a contact or a lead, SOSL finds all of them in one call.

3Example

// SOQL: one object, exact filters
List<Account> big = [SELECT Name FROM Account WHERE AnnualRevenue > 1000000];

// SOSL: a word, across several objects
List<List<SObject>> found = [FIND 'Acme*' IN NAME FIELDS
    RETURNING Account(Id, Name), Contact(Id, Name, Email), Lead(Id, Company)];
List<Account> accounts = (List<Account>) found[0];

4In short - say this in the interview

1What it is

Two directions. Child to parent uses dot notation (Contact.Account.Name). Parent to child uses a subquery on the child relationship name (SELECT … FROM Contacts).

2Why it matters

One relationship query replaces a query per record - fewer queries, fewer loops, and the related data arrives already attached to each record.

3Example

// Child to parent: walk up with a dot (up to five levels)
List<Contact> contacts = [SELECT Name, Account.Name, Account.Owner.Name FROM Contact];

// Parent to child: a subquery on the relationship name
List<Account> accounts = [SELECT Name, (SELECT LastName, Email FROM Contacts) FROM Account];
for (Account a : accounts) {
    for (Contact c : a.Contacts) { System.debug(a.Name + ' - ' + c.LastName); }
}

// A custom relationship ends in __r: (SELECT Amount__c FROM Invoice_Lines__r)

4In short - say this in the interview

1What it is

SOQL can calculate in the database: COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING. The results come back as AggregateResult rows that you read by field name or alias.

2Why it matters

Counting or summing in the query is far cheaper than loading every record and adding them up in Apex - less heap, less CPU, one round trip.

3Example

List<AggregateResult> rows = [
    SELECT StageName, COUNT(Id) deals, SUM(Amount) total
    FROM Opportunity
    WHERE CloseDate = THIS_YEAR
    GROUP BY StageName
    HAVING SUM(Amount) > 100000
];
for (AggregateResult r : rows) {
    System.debug(r.get('StageName') + ': ' + r.get('deals') + ' deals, ' + r.get('total'));
}

4In short - say this in the interview

1What it is

An attack where text a user typed is pasted into a dynamic query string and changes the query itself - reading or filtering records the code never meant to expose.

2Why it matters

Dynamic SOQL (Database.query) is needed for search screens and configurable filters, which is exactly where user input meets the query. One unescaped quote is enough.

3Example

String q = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';
List<Account> rows = Database.query(q);   // input like  x' OR Name != '  matches everything

List<Account> rows = Database.queryWithBinds(
    'SELECT Id FROM Account WHERE Name = :name',
    new Map<String, Object>{ 'name' => userInput },
    AccessLevel.USER_MODE);

4In short - say this in the interview

1What it is

Apex runs in system mode and ignores the user's object and field permissions unless you ask. WITH USER_MODE on a query (and as user on DML) enforces them; Security.stripInaccessible removes the fields a user cannot see while keeping the rest.

2Why it matters

Without it, an LWC backed by Apex can show a salary field to someone whose profile hides it. Sharing keywords do not help - they cover record access only.

3Example

// 1. Enforce sharing, object and field permissions in the query
List<Contact> rows = [SELECT Name, Email FROM Contact WITH USER_MODE];

// 2. The same for DML
insert as user new Contact(LastName = 'Rao');

// 3. Keep what the user may see, drop the rest instead of failing
SObjectAccessDecision d = Security.stripInaccessible(
    AccessType.READABLE, [SELECT Name, Email, Salary__c FROM Contact]);
List<Contact> safe = (List<Contact>) d.getRecords();

4In short - say this in the interview

1What it is

A query whose WHERE clause filters on an indexed field and narrows the result to a small share of the records - so the database can use the index instead of scanning the whole table.

2Why it matters

On large objects, a non-selective query is slow, and inside a trigger on an object with more than 200,000 records Salesforce rejects it outright with a "non-selective query" error.

3Example

4In short - say this in the interview

1What it is

A SOQL for loop iterates over query results in chunks of 200 instead of loading them all into one list. FOR UPDATE locks the selected records until the transaction ends.

2Why it matters

The loop keeps heap usage low when a query returns thousands of large records. FOR UPDATE prevents two transactions changing the same record at once - two payments against one balance, for example.

3Example

// Heap-friendly: 200 records at a time
for (List<Account> chunk : [SELECT Id, Description FROM Account
                            WHERE CreatedDate = LAST_N_DAYS:30]) {
    // work with this chunk; earlier chunks can be freed
}

// Lock the row so no one else changes the balance mid-transaction
Account acc = [SELECT Id, Balance__c FROM Account WHERE Id = :accountId FOR UPDATE];
acc.Balance__c -= payment;
update acc;

4In short - say this in the interview