Salesforce SOQL vs SOSL Explained: Queries, Relationships & Best Practices

💬 In plain words: SOQL (Salesforce Object Query Language) is used to ask precise questions about a single object and its related records. SOSL (Salesforce Object Search Language) is a broad text search across multiple, unrelated objects at the same time. When writing SOQL, remember this golden rule for relationships: query from a child to a parent using dot notation, and query from a parent to its children using a subquery.

Key Points at a Glance

• Targeted vs. Broad: Use SOQL for filtering specific fields on a specific object tree. Use SOSL for searching a text string across many objects (like a global search).
• Traversing Relationships: You can travel up to 5 levels up to a parent object using dot notation (e.g., Contact.Account.Owner.Name). You travel down to child records using subqueries.
• Let the Database Do the Math: Use Aggregate Queries (COUNT, SUM, MAX) to calculate totals in the database instead of looping through thousands of records in Apex.
• Deleted Records: When you delete a record, it goes to the Recycle Bin for 15 days. You can still query it by adding the ALL ROWS keyword to your SOQL statement.

📌 Real-Life Example: One Query, Whole Family
Imagine you need to load an Account, find out the name of the User who owns it, and pull a list of every Contact associated with it. You don't need three queries. You just need one:

SELECT Name, Owner.Name, (SELECT LastName FROM Contacts) FROM Account

We went child-to-parent with a dot (Owner.Name) and parent-to-child with a subquery.

Conversely, if a user simply types "acme" into a global search bar, you don't write a dozen SOQL queries. That's a job for SOSL: FIND 'acme' IN ALL FIELDS.

Understanding Database Queries in Salesforce

To master Salesforce development, you must understand how to interact with the database efficiently. Governor limits dictate that you can only retrieve 50,000 records per transaction, making highly selective, efficient queries a necessity.

• SOQL navigates a single object tree. You can walk up to parents and down to children in one smooth motion.
• Aggregate Queries (using GROUP BY or HAVING) don't return standard sObjects. They return an AggregateResult object, which you must interact with using the .get() method.
• Row Locking: Adding FOR UPDATE at the end of a SOQL query locks those specific records for the duration of your transaction, preventing other users from updating them at the exact same time (a concurrent-update race).
• Dynamic SOQL: Using Database.query() allows you to build a query string at runtime. However, you must always sanitize user inputs to protect against SOQL injection attacks.

🧠 Core Takeaway: Dots up, subquery down. Child→Parent = Dots (Contact.Account.Name). Parent→Child = Subquery. Aggregate calculations return an AggregateResult. Use FOR UPDATE to lock rows. Use SOSL for text searches across multiple objects.

🧭 360 Card — SOQL & SOSL

• Rule: SOQL asks one specific object a precise question. SOSL searches for text strings across many objects simultaneously.
• Gain: One SOQL query can gather an entire hierarchy of data, saving you from hitting your 100-query governor limit.
• Limits: You can only traverse 5 levels up in a single SOQL query. A query that isn't selective (no indexed filters) will perform poorly with Large Data Volumes (LDV).
• Price of Locks: FOR UPDATE locks the row for the whole transaction, meaning other processes have to wait. Use it cautiously.
• Volume Alert: The 50,000 SOQL row retrieval limit is strict per transaction. For anything larger, you must utilize Batch Apex or a Queueable cursor.

⚠ INTERVIEW TRAP: The "Loop-and-Sum" Anti-Pattern. Never query thousands of records just to loop through them in Apex and calculate a total. This burns through your CPU time, fills up your Heap memory, and risks hitting the 50,000 row limit. Always push the math to the database using an Aggregate SOQL query (e.g., SUM(Amount)).

Core Q&A and Common Scenarios

Q: Can you show parent-to-child and child-to-parent relationships in a single query? And when should you use an aggregate query?

🎯 Say this first: "Use dot notation to go up to the parent, and a subquery to go down to the children. Use aggregate queries when you need calculations like sums or counts, so you don't waste memory loading the actual rows."

A: One query can handle both directions effortlessly. You select the parent's fields with dots, and nest a child query in parentheses. When you only need the totals of those children, you swap to an aggregate query with a GROUP BY clause. Let the database do the heavy lifting.

// 1. Both directions in one query:
List<Account> accounts = [
    // Dot notation walks UP to the parent
    SELECT Name, Owner.Name, 
    // A subquery walks DOWN to the children
    (SELECT LastName, Email FROM Contacts) 
    FROM Account 
    WHERE Industry = 'Tech'
];

// 2. Aggregate instead of Loop-and-Sum:
for (AggregateResult ar : [
    // Let the database add up the totals
    SELECT AccountId, SUM(Amount) totalAmount, COUNT(Id) recordCount
    FROM Opportunity 
    WHERE StageName = 'Closed Won'
    // Group by parent, keep only groups above a threshold
    GROUP BY AccountId 
    HAVING SUM(Amount) > 100000
]) {
    // Extract values using .get()
    Id accId = (Id) ar.get('AccountId');
    Decimal total = (Decimal) ar.get('totalAmount');
}

Q: Two users edit the same record at the exact same time, and one update silently overwrites the other. How does FOR UPDATE fix this, and what is the risk?

A: Adding FOR UPDATE to your SOQL query locks the selected rows for the duration of that Apex transaction. If a second user (or automated process) tries to update that same record, it is forced to wait instead of reading a stale value and overwriting the first user's work. This prevents the classic "lost-update race condition."

The Risk: Holding locks increases database contention. Under high concurrency, or if you have Account Data Skew (10,000+ child records tied to one parent), this will cause UNABLE_TO_LOCK_ROW exceptions or deadlocks. Furthermore, you cannot make an HTTP callout while holding a row lock. You must keep transactions that lock rows extremely short and scoped narrowly.

Q: You need to build a global search box that finds a user's text string (case-insensitive) across Accounts, Contacts, and Cases. Do you use SOQL or SOSL?

A: You use SOSL. It is designed to search text indexes across multiple objects in one single transaction.

You would write: FIND 'search term' IN ALL FIELDS RETURNING Account(Name), Contact(FirstName), Case(Subject). This is vastly superior to writing three separate SOQL queries using the LIKE operator.

Why? Because a SOQL query using LIKE '%term%' (with a leading wildcard) cannot use database indexes. It will force a full table scan, which will time out on Large Data Volumes. SOSL is relevance-ranked and index-backed, making it the perfect tool for fuzzy, cross-object text searches.

Q: What happens to a record when it is deleted? How do you query it, or bring it back?

A: In Salesforce, standard deletion is a "soft delete." The record moves to the Recycle Bin and its isDeleted field is set to true.

• It stays in the bin for up to 15 days (though oldest entries are purged first if the bin reaches capacity).
• By default, SOQL queries completely ignore deleted rows. To see them, you must append the ALL ROWS keyword to the end of your SOQL statement (e.g., SELECT Id FROM Account WHERE isDeleted = true ALL ROWS).
• You can restore the record in Apex using the undelete DML statement. This brings the record back, along with all of its Master-Detail children.
• A "Hard Delete" (done via the Bulk API or by manually emptying the Recycle Bin) permanently destroys the record.