SOQL & SOSL Interview Questions & Complete Guide — SFX Support

1. Introduction to SOQL & SOSL

In Salesforce development, interacting with data stored in your organization's database is fundamental. Unlike traditional SQL databases, Salesforce uses its own query languages tailored for its unique object model:

• SOQL (Salesforce Object Query Language): Used to query a single sObject or multiple related sObjects. Similar to the SELECT statement in SQL.
• SOSL (Salesforce Object Search Language): Used to perform text-based searches across multiple sObjects, returning results that match a search term. Similar to a search engine query.

Why are SOQL & SOSL Essential?

• Data Retrieval: Get the exact data you need from Salesforce objects.
• Automation: Power your Apex triggers, classes, Visualforce pages, and Lightning Components with dynamic data.
• Business Logic: Implement complex business rules that depend on querying existing data.
• Reporting & Dashboards: Understanding SOQL helps you comprehend how reports and list views fetch data.

Key Differences

• SOQL: Best for retrieving specific data from a known object (or related objects) when you know the structure and specific fields — like looking up an address in a phone book by name.
• SOSL: Best for searching text across multiple objects and fields when you don't know which object contains the data — like searching a keyword on the internet.

Both query languages are subject to Salesforce's governor limits to ensure efficient use of shared resources.

2. SOQL Basics

SOQL queries are used in Apex, Visualforce controllers, Lightning components, and the Developer Console. They retrieve a list of sObject records.

Basic Syntax

SELECT FieldName1, FieldName2
FROM   ObjectApiName
[WHERE Conditions]
[ORDER BY FieldName [ASC|DESC]]
[LIMIT Number]
[OFFSET Number]

• SELECT: Specifies the fields to retrieve — standard fields, custom fields ending in __c, and relationship fields.
• FROM: The sObject to query (e.g., Account, Contact, MyCustomObject__c).
• WHERE: Filters records using operators like =, !=, LIKE, IN, NOT IN, AND, OR.
• ORDER BY: Sorts results. ASC (default) or DESC.
• LIMIT: Restricts the number of rows returned.
• OFFSET: Skips a specified number of rows — used for pagination.

Examples

SELECT Name, Industry FROM Account

SELECT Id, FirstName, LastName, Email
FROM Contact
WHERE MailingCity = 'San Francisco'

SELECT Id, Name, CreatedDate
FROM Account
WHERE CreatedDate = LAST_N_DAYS:30
ORDER BY Name ASC
LIMIT 10

SELECT Name, StageName, Amount
FROM Opportunity
WHERE StageName = 'Closed Won' AND Amount >= 10000
ORDER BY Amount DESC

Using SOQL in Apex

In Apex, embed SOQL queries directly within square brackets []. The query returns a List of sObjects.

List<Account> topAccounts = [
    SELECT Id, Name, AnnualRevenue
    FROM Account
    ORDER BY AnnualRevenue DESC
    LIMIT 5
];

for (Account acc : topAccounts) {
    System.debug('Account: ' + acc.Name + ', Revenue: ' + acc.AnnualRevenue);
}

// Single record retrieval
Account singleAccount = [SELECT Id, Name FROM Account WHERE Name = 'Acme Corp' LIMIT 1];

// Handle no results with try-catch
try {
    Account notFound = [SELECT Id FROM Account WHERE Name = 'NonExistent' LIMIT 1];
} catch (QueryException e) {
    System.debug('No account found: ' + e.getMessage());
}

3. SOQL Relationship Queries

Salesforce's relational data model allows you to query related records directly within SOQL — reducing the number of queries needed and helping stay within governor limits.

Parent-to-Child (Nested Subquery)

Query child records from a parent object using a subquery in the SELECT clause. The relationship name is typically the plural of the child object (e.g., Contacts for Contact records related to Account).

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

List<Account> accountsWithContacts = [
    SELECT Id, Name, (SELECT Id, FirstName, LastName, Email FROM Contacts)
    FROM Account
    WHERE Name LIKE 'ABC%'
];

for (Account acc : accountsWithContacts) {
    System.debug('Account: ' + acc.Name);
    if (acc.Contacts != null) {
        for (Contact con : acc.Contacts) {
            System.debug('  Contact: ' + con.FirstName + ' ' + con.LastName);
        }
    }
}

Child-to-Parent (Dot Notation)

Query fields from a parent object by traversing the relationship using dot notation. For custom relationships, use __r instead of __c (e.g., MyCustomLookup__r.Name).

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

List<Contact> techContacts = [
    SELECT Id, FirstName, LastName, Account.Name, Account.Industry
    FROM Contact
    WHERE Account.Industry = 'Technology'
];

for (Contact con : techContacts) {
    System.debug('Contact: ' + con.FirstName + ' ' + con.LastName);
    if (con.Account != null) { // Always null-check parent relationships
        System.debug('  Account: ' + con.Account.Name + ' (' + con.Account.Industry + ')');
    }
}

4. SOQL Aggregate Functions & Grouping

SOQL supports aggregate functions to perform calculations across a set of records. These are commonly used with GROUP BY to group results by a specific field.

Common Aggregate Functions

• COUNT() — Number of rows.
• COUNT(FieldName) — Rows where the field is not null.
• COUNT(DISTINCT FieldName) — Unique non-null values.
• SUM(FieldName) — Sum of a numeric field.
• AVG(FieldName) — Average of a numeric field.
• MIN(FieldName) / MAX(FieldName) — Minimum or maximum value.

SELECT AggregateFunction(FieldName), FieldName2
FROM   ObjectApiName
[WHERE Conditions]
GROUP BY FieldName2
[HAVING AggregateConditions]

// Simple count
Integer accountCount = [SELECT COUNT() FROM Account];
System.debug('Total Accounts: ' + accountCount);

// Grouped aggregate — returns List<AggregateResult>
List<AggregateResult> results = [
    SELECT Industry, SUM(AnnualRevenue) totalRevenue
    FROM Account
    GROUP BY Industry
];

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

SELECT StageName, COUNT(Id)
FROM Opportunity
GROUP BY StageName
HAVING COUNT(Id) > 5

5. SOQL For Loops

SOQL For Loops are a highly efficient way to process large query results. They automatically manage memory by retrieving records in batches — ideal for avoiding heap size governor limits.

for (sObject record : [SELECT Id, Name FROM MyObject WHERE ...]) {
    // Process each record
}

Key Advantages

• Automatic Batching: Salesforce fetches records in chunks (~200 at a time) — prevents exceeding heap size limits.
• Memory Efficiency: Records are processed then discarded, keeping memory consumption low.
• Still counts as one SOQL query — DML inside the loop is still subject to per-transaction limits, so bulkification remains critical.

public class LeadProcessor {

    public static void updateProcessedLeads() {
        List<Lead> leadsToUpdate = new List<Lead>();

        for (Lead l : [SELECT Id, Email, Status FROM Lead WHERE Status = 'New']) {
            if (l.Email != null) {
                l.Status = 'Contacted';
                leadsToUpdate.add(l);
            }
        }

        if (!leadsToUpdate.isEmpty()) {
            update leadsToUpdate; // Single DML operation — not inside the loop
            System.debug('Updated ' + leadsToUpdate.size() + ' leads.');
        }
    }
}

For Loop vs. Direct List Assignment

• Use SOQL For Loop: When expecting hundreds to thousands of records — preferred for triggers and batch jobs to avoid heap limits.
• Use List<sObject> directly: When expecting a small number of records and needing the full collection in memory for subsequent operations.

6. Introduction to SOSL

SOSL (Salesforce Object Search Language) is designed for text-based searches across multiple sObject types and fields. It's ideal when you need to find records containing specific words or phrases, but don't necessarily know which object or field holds that information.

When to Use SOSL?

• When you need to search for a phrase across multiple objects simultaneously.
• When you need to search within a single object, but across multiple fields.
• When you need fuzzier text matching rather than precise field values.
• When performing searches similar to the global search bar in the Salesforce UI.

Key Characteristics

• Searches across multiple objects in a single query.
• Returns List<List<sObject>> — each inner list contains records of a specific object type.
• Searches text fields: Name, Email, Phone, TextArea, LongTextArea, RichText.
• Uses a search index for faster results.
• Supports stemming — searching "run" also finds "running" and "ran".

7. SOSL Syntax & Features

FIND 'Search Term'
[IN SearchGroup]
[RETURNING ObjectTypeName [(FieldList)]]
[LIMIT Number]
[OFFSET Number]

• FIND 'Search Term': The phrase or term to search. Supports wildcards — * (zero or more characters), ? (single character).
• IN SearchGroup: Fields to search within — ALL FIELDS (default), NAME FIELDS, EMAIL FIELDS, PHONE FIELDS, SIDEBAR FIELDS.
• RETURNING ObjectTypeName [(FieldList)]: Which sObjects and fields to return. If field list omitted, only Id is returned. Multiple objects can be specified.
• LIMIT / OFFSET: Limit or skip rows per sObject type.

Examples

FIND 'GenePoint' IN ALL FIELDS
RETURNING Account(Id, Name), Contact(Id, FirstName, LastName)

FIND 'uni*' IN ALL FIELDS
RETURNING Account(Id, Name), Contact(Id, Name)

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

Using SOSL in Apex

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

for (List<SObject> sObjectList : searchResults) {
    if (sObjectList.isEmpty()) continue;

    SObjectType objectType = sObjectList[0].getSObjectType();

    if (objectType == Account.SObjectType) {
        for (Account acc : (List<Account>)sObjectList) {
            System.debug('Found Account: ' + acc.Name + ' (' + acc.Id + ')');
        }
    } else if (objectType == Contact.SObjectType) {
        for (Contact con : (List<Contact>)sObjectList) {
            System.debug('Found Contact: ' + con.FirstName + ' ' + con.LastName);
        }
    }
}

8. Choosing Between SOQL & SOSL

Use SOQL When

• You know which object(s) you need to query.
• You need specific fields from a single object or closely related objects.
• You want to filter on exact field values (e.g., WHERE Status = 'Active').
• You need aggregate functions (SUM, AVG, COUNT), ordering, or grouping.
• You need OFFSET for pagination.

SELECT Id, Name, AnnualRevenue,
       (SELECT Id, FirstName, LastName FROM Contacts)
FROM Account
WHERE Industry = 'Technology' AND AnnualRevenue > 500000

Use SOSL When

• You need to search text across multiple objects simultaneously.
• You need to search across multiple fields without specifying each one.
• You don't know which object or field contains the data.
• You want a Google-like search experience within Salesforce data.

FIND 'Marketing Campaign' IN ALL FIELDS
RETURNING Account(Id, Name, Industry), Lead(Id, Name, Status, Company)

SOQL is like a targeted database query — "Give me all Accounts with X criteria and their related Contacts." SOSL is like a search engine — "Find 'X' anywhere it appears across my data."

9. SOQL & SOSL Best Practices

Writing efficient and scalable queries is paramount for avoiding governor limits and ensuring optimal application performance.

General Best Practices

• Never put SOQL/SOSL inside loops: This is a critical governor limit violation. You get only 100 SOQL queries and 20 SOSL queries per transaction.
• Bulkify your queries: Collect all IDs first, then perform a single query using IN :collectionVariable.
• Select only necessary fields: Retrieve only what you need — reduces heap size and improves performance.
• Use the Query Plan Tool: In Developer Console (Ctrl+I), analyze query performance to see which fields are indexed.
• Consider Custom Indexes: Mark frequently filtered custom fields as "External ID" or "Unique" to create an index.

SOQL Specific

• Always use a WHERE clause — filter records as early as possible.
• Use relationship queries wisely — avoid excessive nesting.
• Use SOQL For Loops for large datasets in triggers and batch jobs.

// ❌ BAD — Query inside loop, hits 100 SOQL limit fast
for (Account acc : Trigger.new) {
    Contact c = [SELECT Id FROM Contact WHERE AccountId = :acc.Id LIMIT 1];
}

// ✅ GOOD — Bulkified: one query outside the loop
Set<Id> accountIds = new Set<Id>();
for (Account acc : Trigger.new) {
    accountIds.add(acc.Id);
}

List<Contact> contacts = [
    SELECT Id, Name, AccountId
    FROM Contact
    WHERE AccountId IN :accountIds
];

Map<Id, Contact> contactsByAccountId = new Map<Id, Contact>();
for (Contact con : contacts) {
    contactsByAccountId.put(con.AccountId, con);
}

for (Account acc : Trigger.new) {
    Contact primary = contactsByAccountId.get(acc.Id);
    if (primary != null) {
        // Process...
    }
}

SOSL Specific

• Be specific in FIND: More precise search terms yield more relevant results.
• Use IN SearchGroup judiciously: Specify IN NAME FIELDS instead of IN ALL FIELDS when you know where to search.
• Limit RETURNING objects and fields to only what you genuinely need.
• Performance consideration: SOSL can be slower than highly selective SOQL on large orgs — use it only when the search paradigm is truly appropriate.