🔍 SOQL Part 2 — Advanced
Top 80 Salesforce SOQL Interview Questions 2026
Real questions asked in Salesforce interviews — Dynamic SOQL, Relationships, Aggregates, Governor Limits, Security, Performance and more.
81Questions
Part 2Advanced
FreeNo Signup
80 Questions Index
1SOQL vs SOSL
2Parent-to-Child
3Child-to-Parent
4COUNT() vs COUNT(Id)
5GROUP BY + HAVING
6Dynamic SOQL
7SOQL For Loop
850K Record Limit
9WITH USER_MODE
10Semi-Join
11Anti-Join
12Pagination
13Date Literals
14NULL in SOQL
15Selective Queries
16QueryLocator vs List
17TYPEOF Polymorphic
18NULLS LAST
19SOQL Injection
20AggregateResult
21FOR UPDATE
22Multi-Currency
23FIELDS() keyword
24Bulkification
25Query Plan Tool
26Relationship Names
27LIKE operator
28IN vs NOT IN
29SOQL in Trigger
30External ID Query
31WHERE vs HAVING
32SUM + AVG + MAX
33Date Functions
34Bind Variables
355 Level Traverse
36OpportunityContactRole
37Custom Object Query
38RecordType in SOQL
39SOQL in Test Class
40ALL ROWS keyword
41Aggregate + WHERE
42Map from SOQL
43SOSL RETURNING
44Heap Size + SOQL
45Chatter SOQL
46SOQL in Flow
47Database.query()
48SOQL without FROM
49Sharing + SOQL
50ORDER BY multiple
51Long Text Area
52Deleted Records
53MAX Opp per Account
54CreatedBy.Name
55Formula Fields
56GROUP BY ROLLUP
57SOQL vs API
58ContentDocument
59IsClosed filter
60Multiple Lookups
61SOQL + Apex List
62WHERE Id IN Set
63Territory Queries
64WITH DATA CATEGORY
65FLS + SOQL
66SOQL in LWC
67Wire Adapter
68Async SOQL
69Relationship field
70QueryMore
71GROUP BY CUBE
72SOQL Chaining
73Status + Stage
74Task + Event
75IsDeleted field
76Multi-select Picklist
77SOQL Timeout
78Database.countQuery
79Troubleshoot slow
80Best Practices
SOQL Fundamentals
Q1 to Q15 — Core concepts asked in every round
What is the difference between SOQL and SOSL? When would you use each?
SOQL queries records from a SINGLE object and its relationships. SOSL searches TEXT across MULTIPLE objects simultaneously. Use SOQL when you know the object. Use SOSL when you don't.
| Feature | SOQL | SOSL |
|---|---|---|
| Objects | One object at a time | Multiple simultaneously |
| Field types | All field types | Text, Email, Phone only |
| Use in triggers | Yes | No |
| Query limit | 100 per transaction | 20 per transaction |
| Records returned | 50,000 max | 2,000 per object |
| Return type | List of sObjects | List of List of sObjects |
// SOQL — query specific object
List<Account> accs = [SELECT Id, Name FROM Account WHERE Industry = 'Tech'];
// SOSL — search across Contact, Account, Lead at once
List<List<sObject>> results = [FIND 'Acme' IN ALL FIELDS
RETURNING Account(Id,Name), Contact(Id,Name), Lead(Id,Name)];
One-liner: "SOQL is for precise queries on a known object — SOSL is for global text search across multiple objects when you don't know where the record lives."
Write a SOQL query to get all Accounts with their related Contacts and Opportunities in one query.
Use Parent-to-Child subqueries — nest child objects inside the parent SELECT using their relationship names (plural for standard objects).
List<Account> accounts = [
SELECT Id, Name, Industry,
(SELECT Id, FirstName, LastName, Email FROM Contacts),
(SELECT Id, Name, StageName, Amount FROM Opportunities)
FROM Account
WHERE Industry = 'Technology'
LIMIT 200
];
for(Account acc : accounts) {
for(Contact con : acc.Contacts) {
System.debug(con.Email);
}
}
Key Rule
Standard child relationship names are always PLURAL — Contacts, Opportunities, Cases. Custom objects use RelationshipName__r (the name defined on the lookup field).One-liner: "Parent-to-Child subquery uses the plural child relationship name in parentheses inside the parent SELECT — Contacts, Opportunities, Cases for standard objects, RelationshipName__r for custom."
Write a SOQL query on Contact to get the Contact name, Account name, and Account Industry in one query.
Use Child-to-Parent traversal — from the child (Contact), use dot notation to access parent (Account) fields directly. No subquery needed.
List<Contact> contacts = [
SELECT Id, FirstName, LastName,
Account.Name,
Account.Industry,
Account.BillingCity
FROM Contact
WHERE Account.Industry = 'Healthcare'
];
for(Contact c : contacts) {
System.debug(c.Account.Name + ' - ' + c.Account.Industry);
}
// Custom lookup: use RelationshipName__r.FieldName
One-liner: "Child-to-Parent uses dot notation — Account.Name from Contact. Up to 5 levels deep. Custom lookups use RelationshipName__r. No subquery needed — just traverse up the chain."
What is the difference between COUNT() and COUNT(Id) in SOQL?
COUNT() returns total rows as an Integer directly — no GROUP BY needed. COUNT(Id) is an aggregate function returning AggregateResult — used with GROUP BY. COUNT(fieldName) counts only non-null values of that field.
// COUNT() — returns Integer directly, no AggregateResult
Integer total = [SELECT COUNT() FROM Account WHERE Industry = 'Tech'];
// COUNT(Id) — returns AggregateResult, used with GROUP BY
List<AggregateResult> grouped = [
SELECT Industry, COUNT(Id) total
FROM Account GROUP BY Industry
];
// COUNT(Phone) — counts only non-null Phone values
List<AggregateResult> withPhone = [
SELECT COUNT(Phone) phoneCount FROM Account
];
One-liner: "COUNT() returns an Integer — use it for a simple count. COUNT(Id) returns AggregateResult — use it with GROUP BY. COUNT(field) counts only non-null values."
Write a query to find all Industries with more than 5 Accounts. Explain GROUP BY and HAVING.
GROUP BY groups records by a field. HAVING filters those groups — it is the only way to filter on aggregate functions. WHERE filters individual records BEFORE grouping. HAVING filters groups AFTER grouping.
List<AggregateResult> results = [
SELECT Industry, COUNT(Id) accountCount
FROM Account
WHERE Industry != null // WHERE filters before GROUP BY
GROUP BY Industry
HAVING COUNT(Id) > 5 // HAVING filters after GROUP BY
ORDER BY COUNT(Id) DESC
];
for(AggregateResult ar : results) {
System.debug(ar.get('Industry') + ': ' + ar.get('accountCount'));
}
One-liner: "GROUP BY groups records, HAVING filters those groups — you cannot use WHERE COUNT(Id) > 5 because WHERE runs before aggregation. HAVING is WHERE for aggregate results."
What is Dynamic SOQL? When do you use it and what is the risk?
Dynamic SOQL builds a query string at runtime using String variables and executes with Database.query(). Use it when the object name, fields, or conditions aren't known at compile time — like user-built report filters.
// Static SOQL — safer, compiled at build time
List<Account> accs = [SELECT Id FROM Account WHERE Name = :searchName];
// Dynamic SOQL — built at runtime
String query = 'SELECT Id, Name FROM Account WHERE Industry = \''
+ String.escapeSingleQuotes(userInput) + '\'';
List<sObject> results = Database.query(query);
// Best practice — use Database.queryWithBinds() (newer API)
Map<String,Object> binds = new Map<String,Object>{'ind' => userInput};
List<sObject> safe = Database.queryWithBinds(
'SELECT Id FROM Account WHERE Industry = :ind', binds,
AccessLevel.USER_MODE
);
One-liner: "Dynamic SOQL uses Database.query() with a String — use it when object or fields aren't known at compile time. Always escape user input with String.escapeSingleQuotes() or use Database.queryWithBinds() to prevent SOQL injection."
What is a SOQL for loop and why use it for large datasets?
A SOQL for loop processes 200 records at a time without loading all into heap memory — critical for avoiding the 6MB heap size limit when processing thousands of records.
// WRONG — loads ALL records into heap at once
List<Account> all = [SELECT Id FROM Account]; // 50K in heap = heap error!
// RIGHT — SOQL for loop, 200 records at a time
for(Account acc : [SELECT Id, Name FROM Account]) {
// Only 200 in memory at any time
}
// BEST — chunked loop with DML per chunk
List<Account> toUpdate = new List<Account>();
for(List<Account> chunk : [SELECT Id, Name FROM Account]) {
for(Account a : chunk) { a.Description = 'Done'; toUpdate.add(a); }
update toUpdate; toUpdate.clear();
}
One-liner: "SOQL for loop processes 200 records at a time — avoids the 6MB heap size limit. Always use it over List when querying large datasets in Apex."
What are the key SOQL governor limits? How do you process more than 50,000 records?
Key limits: 100 SOQL queries per sync transaction, 50,000 records returned per transaction. For 50K+ records, use Batch Apex with Database.QueryLocator which supports up to 50 million records.
| Limit | Sync | Async |
|---|---|---|
| SOQL queries | 100 | 200 |
| Records returned | 50,000 | 50,000 per execute() |
| QueryLocator | N/A | 50,000,000 |
| Heap size | 6 MB | 12 MB |
One-liner: "50K record limit per transaction. For more, use Batch Apex with Database.QueryLocator — it supports 50 million records by processing in chunks of 200 with fresh limits each execute() call."
What is WITH USER_MODE and WITH SYSTEM_MODE? How is it different from WITH SECURITY_ENFORCED?
WITH USER_MODE enforces the running user's FLS and CRUD — only returns fields the user can access. WITH SYSTEM_MODE bypasses all FLS. WITH SECURITY_ENFORCED is DEPRECATED in API v67 (Summer 26).
// WITH USER_MODE — respects FLS (recommended, Summer 26+)
List<Account> accs = [SELECT Id, Name, Salary__c FROM Account WITH USER_MODE];
// FieldAccessException if user can't see Salary__c
// WITH SYSTEM_MODE — bypasses FLS
List<Account> all = [SELECT Id, Salary__c FROM Account WITH SYSTEM_MODE];
// DEPRECATED — do NOT use in API v67+
// [SELECT Id FROM Account WITH SECURITY_ENFORCED]
// Replace with: [SELECT Id FROM Account WITH USER_MODE]
Summer 26 Breaking Change
API v67 defaults Apex to USER_MODE. Classes relying on implicit system mode will throw FieldAccessException. Audit all Apex before upgrading!One-liner: "WITH USER_MODE enforces FLS, WITH SYSTEM_MODE bypasses it, WITH SECURITY_ENFORCED is deprecated in Summer 26. In API v67, USER_MODE is now the default for all Apex."
What is a Semi-Join in SOQL? Write a query to get Contacts whose Account has at least one open Opportunity.
A Semi-Join filters records where a related record EXISTS — using WHERE Id IN (subquery). More efficient than querying both objects separately and joining in Apex.
// Semi-Join — Contacts where Account has open Opportunity
List<Contact> contacts = [
SELECT Id, Name, Email
FROM Contact
WHERE AccountId IN (
SELECT AccountId FROM Opportunity
WHERE IsClosed = false
)
];
// Rules: subquery returns ONE Id field, no LIMIT, no ORDER BY
One-liner: "Semi-Join uses WHERE Id IN (SELECT Id FROM Child WHERE...) to filter based on existence of related records — one SOQL instead of two with Apex join logic."
Write a SOQL query to get all Accounts that have NO Opportunities (Anti-Join).
Use NOT IN with a subquery — an Anti-Join returns records where NO matching child exists. Always filter null relationship fields in the subquery.
// Anti-Join — Accounts with zero Opportunities
List<Account> noOpps = [
SELECT Id, Name
FROM Account
WHERE Id NOT IN (
SELECT AccountId FROM Opportunity
WHERE AccountId != null // Always filter null!
)
];
// Contacts inactive in last 30 days
List<Contact> inactive = [
SELECT Id, Name FROM Contact
WHERE Id NOT IN (
SELECT ContactId FROM Case
WHERE CreatedDate = LAST_N_DAYS:30
AND ContactId != null
)
];
One-liner: "Anti-Join uses NOT IN to return records with no matching child. Always add AND RelationshipField != null in subquery — null AccountIds can cause unexpected results."
How do you implement pagination in SOQL? What is the OFFSET limit?
LIMIT controls page size, OFFSET skips records. OFFSET has a hard max of 2,000 records. For larger datasets use cursor-based pagination using the last Id.
// OFFSET pagination — max 2,000 records
Integer pageSize = 10;
Integer pageNum = 3;
List<Account> page = [
SELECT Id, Name FROM Account
ORDER BY Name
LIMIT :pageSize
OFFSET :(pageNum - 1) * pageSize // = 20
];
// Cursor pagination — works for millions
Id lastId = firstPage[firstPage.size()-1].Id;
List<Account> nextPage = [
SELECT Id, Name FROM Account
WHERE Id > :lastId
ORDER BY Id LIMIT 10
];
One-liner: "OFFSET pagination is simple but limited to 2,000 records. Cursor pagination using WHERE Id > lastId works for millions — store the last record Id and use it as the cursor for the next page."
What are SOQL Date Literals? Write a query for Opportunities closing in the next 30 days.
Date Literals are dynamic date ranges that adjust automatically relative to today — no hardcoded dates needed. Preferred over Date.today() calculations in WHERE clauses.
List<Opportunity> closing = [
SELECT Id, Name, Amount, CloseDate
FROM Opportunity
WHERE CloseDate = NEXT_N_DAYS:30
AND IsClosed = false
ORDER BY CloseDate ASC
];
// Common date literals:
// TODAY, YESTERDAY, TOMORROW
// THIS_WEEK, LAST_WEEK, NEXT_WEEK
// THIS_MONTH, LAST_MONTH, NEXT_MONTH
// THIS_QUARTER, LAST_QUARTER, NEXT_QUARTER
// THIS_YEAR, LAST_YEAR, NEXT_FISCAL_YEAR
// LAST_N_DAYS:n, NEXT_N_DAYS:n
One-liner: "Date Literals like NEXT_N_DAYS:30 automatically adjust relative to today — no Apex date calculation needed. They make SOQL cleaner and run correctly regardless of when the query executes."
How do you query for NULL and NOT NULL values in SOQL?
SOQL uses = null (not IS NULL) and != null (not IS NOT NULL). Use ORDER BY field NULLS LAST to push nulls to the end of sorted results.
// IS NULL equivalent
List<Account> noIndustry = [SELECT Id FROM Account WHERE Industry = null];
// IS NOT NULL equivalent
List<Contact> hasPhone = [SELECT Id FROM Contact WHERE Phone != null];
// NULLS LAST — push null values to end
[SELECT Id, AnnualRevenue FROM Account
ORDER BY AnnualRevenue DESC NULLS LAST]
One-liner: "SOQL uses = null and != null — not IS NULL / IS NOT NULL like SQL. Use ORDER BY field DESC NULLS LAST to sort correctly when nulls are present."
What makes a SOQL query selective? Why does it matter?
A selective query uses indexed fields filtering less than 10% of records (or under 333,333 records). Salesforce uses the index instead of a full table scan — dramatically faster on large objects.
Default Indexed Fields
Id, Name, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, Master-Detail fields, Lookup fields, External IDs, Unique fields. Non-indexed fields on objects with millions of records cause full table scans and timeouts.// NON-SELECTIVE — full table scan (slow on 1M records)
[SELECT Id FROM Account WHERE Description = 'VIP']
// SELECTIVE — uses index on Name field (fast)
[SELECT Id FROM Account WHERE Name = 'Acme Corp']
// Check: Developer Console → Query Plan tool
One-liner: "Selective queries use indexed fields filtering under 10% of records — Salesforce uses the index instead of scanning every row. Use the Query Plan tool to check cost below 1 means index will be used."
🎯 Test Your SOQL Knowledge
100 premium MCQ questions on SOQL and SOSL — timed, scored, with detailed explanations.
One password unlocks all 22 premium quizzes.
One password unlocks all 22 premium quizzes.
Advanced SOQL Techniques
Q16 to Q35 — Deeper concepts for senior roles
What is the difference between Database.QueryLocator and Iterable in Batch Apex start() method?
Database.QueryLocator supports 50 million records — Salesforce handles chunking automatically. Iterable (List) is limited to 50,000 records but lets you define custom logic to determine which records to process.
| Feature | QueryLocator | Iterable |
|---|---|---|
| Max records | 50,000,000 | 50,000 |
| Defined by | SOQL string | Custom Apex logic |
| Chunking | Automatic | Manual control |
| Use when | Large data, simple SOQL filter | Complex scope logic |
One-liner: "QueryLocator handles 50 million records automatically — use it for large data. Iterable gives custom control over scope — use it when you need complex logic to determine which records to process."
What is a polymorphic relationship in SOQL? How do you use TYPEOF?
Polymorphic fields can point to multiple object types. Task.WhoId = Contact or Lead. Task.WhatId = Account, Opportunity, Case. Use TYPEOF in SOQL to handle different types and instanceof in Apex to cast.
List<Task> tasks = [
SELECT Id, Subject,
TYPEOF Who
WHEN Contact THEN FirstName, LastName, Email
WHEN Lead THEN FirstName, LastName, Company
END
FROM Task WHERE CreatedDate = TODAY
];
for(Task t : tasks) {
if(t.Who instanceof Contact) {
Contact c = (Contact) t.Who;
} else if(t.Who instanceof Lead) {
Lead l = (Lead) t.Who;
}
}
One-liner: "Polymorphic fields like Task.WhoId point to different object types. Use TYPEOF in SOQL to branch field selection by type, and instanceof in Apex to check and cast the result."
How does NULLS FIRST and NULLS LAST work in ORDER BY?
By default ASC puts NULLs first, DESC puts NULLs last. Override with NULLS LAST or NULLS FIRST to control where null values appear.
// Default: ASC puts nulls FIRST
[SELECT Id, AnnualRevenue FROM Account ORDER BY AnnualRevenue ASC]
// NULLS LAST — push nulls to end (most useful)
[SELECT Id, AnnualRevenue FROM Account ORDER BY AnnualRevenue DESC NULLS LAST]
// Multiple sort fields
[SELECT Id, Industry, AnnualRevenue FROM Account
ORDER BY Industry ASC NULLS LAST, AnnualRevenue DESC NULLS LAST]
One-liner: "ASC puts NULLs first by default, DESC puts NULLs last. Use NULLS LAST with ASC or NULLS FIRST with DESC to override — always use NULLS LAST when users expect empty values at the bottom."
What is SOQL Injection? Show vulnerable code and how to fix it.
SOQL Injection is when user input is concatenated directly into a Dynamic SOQL string — an attacker manipulates the WHERE clause to access unauthorized records or bypass security.
// VULNERABLE — direct concatenation
String query = 'SELECT Id FROM Account WHERE Name = \'' + userInput + '\'';
// If userInput = test' OR Name != ' → returns ALL Accounts!
// FIX 1 — escape user input
String safe = String.escapeSingleQuotes(userInput);
String query2 = 'SELECT Id FROM Account WHERE Name = \'' + safe + '\'';
// FIX 2 — static SOQL with bind variable (BEST)
List<Account> accs = [SELECT Id FROM Account WHERE Name = :userInput];
// Bind variables cannot be injected — Salesforce handles escaping
One-liner: "SOQL Injection is user input manipulating the WHERE clause in Dynamic SOQL. Fix with String.escapeSingleQuotes() or better, use static SOQL bind variables (:variable) which are injection-proof."
How do you access values from AggregateResult in Apex? Why should you always use aliases?
Use ar.get('aliasName') — passing the alias as a String. Without an alias, Salesforce auto-generates expr0, expr1 which is unreliable and breaks if field order changes.
// Always alias your aggregate functions
List<AggregateResult> results = [
SELECT Industry,
COUNT(Id) totalAccounts,
SUM(AnnualRevenue) totalRevenue,
AVG(AnnualRevenue) avgRevenue,
MAX(AnnualRevenue) maxRevenue
FROM Account
WHERE Industry != null
GROUP BY Industry
];
for(AggregateResult ar : results) {
String industry = (String) ar.get('Industry');
Integer count = (Integer) ar.get('totalAccounts');
Decimal revenue = (Decimal) ar.get('totalRevenue');
}
One-liner: "Always alias aggregate functions — COUNT(Id) totalCount — then access with ar.get('totalCount'). Without alias, Salesforce auto-generates expr0 which breaks if you reorder fields."
What is FOR UPDATE in SOQL? When would you use it?
FOR UPDATE locks queried records so no other transaction can modify them until the current transaction completes. Use to prevent race conditions when multiple processes update the same records.
List<Opportunity> opps = [
SELECT Id, Amount
FROM Opportunity
WHERE StageName = 'Negotiation'
FOR UPDATE
];
// Other transactions trying to update these will WAIT
for(Opportunity opp : opps) { opp.Amount *= 1.1; }
update opps; // Lock released after this
// Limitations:
// Cannot use with LIMIT or ORDER BY
// Can cause deadlocks — avoid in Batch Apex
One-liner: "FOR UPDATE locks records during a transaction to prevent race conditions. Cannot use with LIMIT or ORDER BY. Avoid in Batch Apex — can cause deadlocks when multiple batches run simultaneously."
How does SOQL work in a multi-currency org? What is CONVERT_CURRENCY()?
Currency fields store values in the record's own currency. CONVERT_CURRENCY() converts them to the running user's currency at the current conversion rate — essential for aggregating amounts across different currencies.
// Standard query — returns Amount in record's own currency
[SELECT Id, Amount, CurrencyIsoCode FROM Opportunity]
// CONVERT_CURRENCY — converts to user's currency
List<AggregateResult> pipeline = [
SELECT StageName,
SUM(CONVERT_CURRENCY(Amount)) totalConverted
FROM Opportunity
WHERE IsClosed = false
GROUP BY StageName
];
One-liner: "CONVERT_CURRENCY() converts currency fields to the running user's currency at current rates — essential for SUM/AVG across a multi-currency org where records have different base currencies."
What is the FIELDS() keyword in SOQL? What are FIELDS(ALL), FIELDS(STANDARD), FIELDS(CUSTOM)?
FIELDS() queries multiple fields without listing them. FIELDS(ALL) = all fields, FIELDS(STANDARD) = all standard fields, FIELDS(CUSTOM) = all custom fields. Requires LIMIT 200.
// FIELDS(ALL) — all fields, LIMIT 200 required
List<Account> all = [SELECT FIELDS(ALL) FROM Account LIMIT 200];
// FIELDS(STANDARD) — standard fields only
List<Account> std = [SELECT FIELDS(STANDARD) FROM Account LIMIT 200];
// FIELDS(CUSTOM) — custom fields only
List<Account> cust = [SELECT FIELDS(CUSTOM) FROM Account LIMIT 200];
// Mix with specific fields
List<Account> mixed = [SELECT Id, FIELDS(CUSTOM) FROM Account LIMIT 200];
One-liner: "FIELDS(ALL/STANDARD/CUSTOM) queries multiple fields without listing each one — always requires LIMIT 200. Great for debugging and data inspection but avoid in triggers due to performance overhead."
You have a trigger processing 300 Opportunities. You need the related Account for each. How do you avoid SOQL governor limit errors?
Collect all AccountIds in a Set, query all Accounts in ONE SOQL, store in a Map by Id, then use the Map in the loop. Never SOQL inside a loop.
// WRONG — 300 SOQL queries for 300 records!
for(Opportunity opp : Trigger.new) {
Account acc = [SELECT Id FROM Account WHERE Id = :opp.AccountId]; // LIMIT HIT!
}
// RIGHT — 1 SOQL for all 300 records
Set<Id> accIds = new Set<Id>();
for(Opportunity opp : Trigger.new) accIds.add(opp.AccountId);
Map<Id,Account> accMap = new Map<Id,Account>(
[SELECT Id, Name, Industry FROM Account WHERE Id IN :accIds]
);
for(Opportunity opp : Trigger.new) {
Account acc = accMap.get(opp.AccountId); // Zero SOQL — Map lookup
}
One-liner: "Collect all Ids in a Set → query once using WHERE Id IN :idSet → store in Map by Id → use Map in loop. One SOQL for 300 records instead of 300 SOQLs."
How do you use the SOQL Query Plan tool to debug a slow query?
Developer Console → Help → Preferences → Enable Query Plan. Enter SOQL and click Query Plan. Look at Leading Operation Type (Index vs TableScan) and Cost (below 1 = index used, above 1 = slow).
// SLOW — TableScan, Cost = 3.2
SELECT Id FROM Account WHERE Description = 'Enterprise'
// Description not indexed → full table scan
// FAST — Index, Cost = 0.03
SELECT Id FROM Account WHERE Name = 'Acme Corp'
// Name is indexed → uses index
// Fixes:
// 1. Use indexed fields in WHERE
// 2. Mark field as External ID (auto-index)
// 3. Request custom index via Salesforce Support
One-liner: "Query Plan tool shows Index vs TableScan and cost for your SOQL — cost below 1 means index, above 1 means full scan. Fix slow queries by using indexed fields or requesting a custom index via Salesforce Support."
What is the difference between standard and custom relationship names in SOQL?
Standard objects have predefined relationship names — Contacts, Opportunities, Cases (plural) for Parent-to-Child, Account, Contact (singular) for Child-to-Parent. Custom objects use the relationship name defined on the lookup field + __r suffix.
// Standard Parent-to-Child (plural relationship name)
[SELECT Name, (SELECT Name FROM Contacts) FROM Account]
[SELECT Name, (SELECT Name FROM Opportunities) FROM Account]
// Standard Child-to-Parent (singular field name)
[SELECT Name, Account.Name FROM Contact]
[SELECT Name, Account.Name FROM Opportunity]
// Custom object — lookup field = Region__c, relationship = Region__r
[SELECT Name, Region__r.Name FROM Territory__c]
[SELECT Name, (SELECT Name FROM Territories__r) FROM Region__c]
One-liner: "Standard relationships use predefined names — Contacts, Opportunities plural for child queries. Custom objects use the relationship name from the lookup field definition plus __r suffix."
How does the LIKE operator work in SOQL? What wildcards are supported?
LIKE performs case-insensitive pattern matching. % matches any sequence of characters. _ matches exactly one character. Use LIKE for partial text matching — note it only works on text fields.
// % — matches any sequence (case-insensitive)
[SELECT Id, Name FROM Account WHERE Name LIKE '%Acme%'] // contains Acme
[SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'] // starts with Acme
[SELECT Id, Name FROM Account WHERE Name LIKE '%Corp'] // ends with Corp
// _ — matches exactly one character
[SELECT Id FROM Account WHERE Name LIKE 'A_me%'] // Acme, Ayme, etc.
// Dynamic bind variable with LIKE
String search = '%' + String.escapeSingleQuotes(userInput) + '%';
[SELECT Id, Name FROM Account WHERE Name LIKE :search]
One-liner: "LIKE uses % for any characters and _ for one character — it's case-insensitive. Use LIKE '%text%' for contains, 'text%' for starts with, '%text' for ends with."
How do you use IN and NOT IN with a Set or List in SOQL?
Use IN and NOT IN with a bind variable containing a Set or List. This is one of the most common patterns for bulkified triggers and batch processing in Salesforce.
// IN with Set — most common pattern
Set<Id> accountIds = new Set<Id>{'001xx000...', '001xx001...'};
[SELECT Id, Name FROM Account WHERE Id IN :accountIds]
// NOT IN with Set
Set<String> excludedStages = new Set<String>{'Closed Won', 'Closed Lost'};
[SELECT Id FROM Opportunity WHERE StageName NOT IN :excludedStages]
// IN with List of strings
List<String> industries = new List<String>{'Technology', 'Healthcare'};
[SELECT Id, Name FROM Account WHERE Industry IN :industries]
One-liner: "IN and NOT IN with bind variables accept Set or List — use Set for Id lookups (no duplicates), List for ordered or string values. This pattern is the foundation of all bulkified Apex."
What are the SOQL rules inside Apex Triggers? What is the most common mistake?
SOQL inside triggers counts toward the 100 query limit per transaction. The most common mistake is putting SOQL inside a for loop — this causes limit errors when bulk operations run. Always query before the loop.
// WRONG — SOQL inside loop (fails at 100 records)
trigger OppTrigger on Opportunity(before insert) {
for(Opportunity opp : Trigger.new) {
Account acc = [SELECT Id FROM Account WHERE Id = :opp.AccountId];
}
}
// RIGHT — one SOQL before loop
trigger OppTrigger on Opportunity(before insert) {
Set<Id> ids = new Set<Id>();
for(Opportunity o : Trigger.new) ids.add(o.AccountId);
Map<Id,Account> accs = new Map<Id,Account>([SELECT Id, Name FROM Account WHERE Id IN :ids]);
for(Opportunity o : Trigger.new) { Account a = accs.get(o.AccountId); }
}
One-liner: "SOQL in triggers counts toward the 100-query limit. Never put SOQL inside a for loop — collect Ids first, query once with WHERE Id IN :set, then use a Map to access records in the loop."
How do you query records by External ID in SOQL?
External ID fields are automatically indexed — queries on External IDs are always selective and fast. Upsert also uses External IDs. Query them like any other field using WHERE ExternalIdField__c = value.
// Query by External ID
Account acc = [SELECT Id, Name FROM Account
WHERE SAP_Account_Id__c = 'SAP-001234'
LIMIT 1];
// Multiple External IDs
Set<String> sapIds = new Set<String>{'SAP-001', 'SAP-002', 'SAP-003'};
List<Account> accs = [SELECT Id, Name, SAP_Account_Id__c
FROM Account
WHERE SAP_Account_Id__c IN :sapIds];
// External ID in upsert — no SOQL needed
Account a = new Account(SAP_Account_Id__c = 'SAP-001', Name = 'Acme');
upsert a SAP_Account_Id__c; // Auto-match or create
One-liner: "External ID fields are auto-indexed — always selective and fast. Query with WHERE ExternalId__c = value. Use in upsert operations to match records from external systems without a prior SOQL."
Can you use both WHERE and HAVING in the same SOQL query? Show an example.
Yes — WHERE and HAVING can both be used in the same query. WHERE filters individual records before grouping. HAVING filters the aggregated groups after grouping. Both apply at different stages of the query execution.
// WHERE + HAVING in same query
List<AggregateResult> results = [
SELECT AccountId, Account.Name, SUM(Amount) total
FROM Opportunity
WHERE IsClosed = true // WHERE: filter records before grouping
AND CloseDate = THIS_YEAR
GROUP BY AccountId, Account.Name
HAVING SUM(Amount) > 100000 // HAVING: filter groups after aggregation
ORDER BY SUM(Amount) DESC
];
One-liner: "WHERE filters records BEFORE grouping (individual rows), HAVING filters AFTER grouping (aggregate groups). Both can be used together in one query."
Write a SOQL query to get SUM, AVG, MAX, MIN of Opportunity Amount grouped by Stage.
All aggregate functions can be used in one query along with GROUP BY. Each function ignores NULL values except COUNT().
List<AggregateResult> stats = [
SELECT StageName,
COUNT(Id) count,
SUM(Amount) total,
AVG(Amount) average,
MAX(Amount) highest,
MIN(Amount) lowest
FROM Opportunity
WHERE Amount != null
GROUP BY StageName
ORDER BY SUM(Amount) DESC
];
for(AggregateResult ar : stats) {
Decimal total = (Decimal) ar.get('total');
Decimal avg = (Decimal) ar.get('average');
}
One-liner: "SUM, AVG, MAX, MIN all work in one GROUP BY query — all ignore NULL values. COUNT() is the exception — it counts all rows including those with null fields."
How do you use SOQL date functions like CALENDAR_MONTH() in GROUP BY?
Date functions like CALENDAR_MONTH(), CALENDAR_YEAR(), CALENDAR_QUARTER() extract parts of a date for grouping — useful for time-based analytics without Apex date calculations.
// Group Opportunities by month
List<AggregateResult> byMonth = [
SELECT CALENDAR_MONTH(CloseDate) month,
CALENDAR_YEAR(CloseDate) year,
SUM(Amount) total
FROM Opportunity
WHERE CloseDate = THIS_YEAR
AND IsClosed = true
GROUP BY CALENDAR_MONTH(CloseDate), CALENDAR_YEAR(CloseDate)
ORDER BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)
];
// Other functions: WEEK_IN_YEAR, DAY_IN_MONTH, HOUR_IN_DAY
One-liner: "Date functions like CALENDAR_MONTH(CloseDate) extract date parts for GROUP BY — useful for monthly/quarterly reports directly in SOQL without Apex date arithmetic."
What are bind variables in SOQL and why are they preferred over string concatenation?
Bind variables use a colon prefix (:variable) to pass Apex values into SOQL. They are safer (injection-proof), cleaner, and can reference any Apex variable, collection, or sObject field in scope.
// Bind variables — safe, clean, preferred
String industry = 'Technology';
Date closeDate = Date.today().addDays(30);
Set<Id> ownerIds = new Set<Id>{UserInfo.getUserId()};
[SELECT Id, Name FROM Account WHERE Industry = :industry]
[SELECT Id FROM Opportunity WHERE CloseDate <= :closeDate]
[SELECT Id FROM Opportunity WHERE OwnerId IN :ownerIds]
// Can even use sObject fields
Account a = [SELECT Id, Name FROM Account LIMIT 1];
[SELECT Id FROM Contact WHERE AccountId = :a.Id]
One-liner: "Bind variables (:variable) pass Apex values into SOQL safely — no injection risk, no string escaping needed. They can reference strings, Ids, Sets, Lists, Dates, and even sObject fields."
How many levels of parent relationship can you traverse in SOQL? Show the deepest possible example.
SOQL supports up to 5 levels of parent relationship traversal using dot notation. Each dot traverses one level up the relationship chain.
// 5-level traversal — Contact up through Owner's Profile's User
[SELECT Id, Name,
Account.Name, // Level 1
Account.Owner.Name, // Level 2
Account.Owner.Profile.Name, // Level 3
Account.Owner.Manager.Name, // Level 4
Account.Owner.Manager.Profile.Name // Level 5 — max!
FROM Contact]
// Common real-world multi-level
[SELECT Name, Opportunity.Account.Name, Opportunity.Account.Owner.Name
FROM OpportunityContactRole]
One-liner: "SOQL supports up to 5 levels of Child-to-Parent dot notation traversal. Beyond 5 levels throws an error. Plan your relationship queries to stay within this limit."
Real Interview Scenario Questions
Q36 to Q60 — Scenario-based questions from actual interviews
How do you query Contacts linked to an Opportunity through OpportunityContactRole?
Contact and Opportunity are not directly related — connected through OpportunityContactRole junction object. Query OCR and traverse to both Contact and Opportunity using dot notation.
// Query from OCR — traverse to both Contact and Opportunity
List<OpportunityContactRole> ocrs = [
SELECT Contact.Name, Contact.Email, Role,
Opportunity.Name, Opportunity.StageName
FROM OpportunityContactRole
WHERE Opportunity.StageName = 'Closed Won'
];
// Unique Contacts only — no duplicates
List<Contact> contacts = [
SELECT Id, Name, Email FROM Contact
WHERE Id IN (
SELECT ContactId FROM OpportunityContactRole
WHERE Opportunity.StageName = 'Closed Won'
)
];
One-liner: "Contact and Opportunity are linked via OpportunityContactRole junction object — query from OCR and traverse to Contact.Name and Opportunity.StageName simultaneously using dot notation."
How do you write SOQL queries on custom objects?
Custom objects use __c suffix in FROM clause. Custom fields use __c suffix in SELECT. Child relationship queries use the relationship name plus __r suffix. Everything else is identical to standard SOQL.
// Query custom object
List<Order__c> orders = [
SELECT Id, Name, Amount__c, Status__c, Account__r.Name
FROM Order__c
WHERE Status__c = 'Pending'
AND Amount__c > 10000
ORDER BY CreatedDate DESC
];
// Parent-to-Child on custom object
List<Account__c> accounts = [
SELECT Id, Name__c,
(SELECT Id, Amount__c FROM Orders__r) // relationship name__r plural
FROM Account__c
];
One-liner: "Custom objects use __c in FROM and field names. Child relationship queries use RelationshipName__r (plural). Parent traversal uses __r in dot notation. Everything else is identical to standard SOQL."
How do you filter by RecordType in SOQL?
Filter using RecordTypeId with a subquery on RecordType object, or traverse RecordType.DeveloperName for a hardcoded name. Using DeveloperName is safer than Id as Ids differ between orgs.
// Filter by RecordType DeveloperName (org-independent)
List<Account> enterprise = [
SELECT Id, Name
FROM Account
WHERE RecordType.DeveloperName = 'Enterprise'
];
// Filter by RecordTypeId using subquery
List<Account> byId = [
SELECT Id, Name FROM Account
WHERE RecordTypeId = (
SELECT Id FROM RecordType
WHERE SObjectType = 'Account'
AND DeveloperName = 'Enterprise'
LIMIT 1
)
];
One-liner: "Use RecordType.DeveloperName = 'Name' in WHERE to filter by record type — DeveloperName is org-independent unlike RecordTypeId which differs between sandbox and production."
How does SOQL work in Apex Test classes? What is seeAllData?
By default test classes run in an isolated context — SOQL only returns records created in the test method. With @isTest(seeAllData=true), SOQL can access org data — avoid this as it creates unreliable tests.
@isTest
private class AccountTest {
// Default — isolated, no org data visible in SOQL
@isTest
static void testWithTestData() {
Account a = new Account(Name = 'Test');
insert a;
// SOQL only returns records inserted in this test
List<Account> result = [SELECT Id FROM Account];
System.assertEquals(1, result.size());
}
// seeAllData=true — can access org records (avoid!)
@isTest(seeAllData=true)
static void testWithOrgData() {
// SOQL returns real org data — test becomes org-dependent!
}
}
One-liner: "Test SOQL only sees records created in the test by default — seeAllData=true allows org data but makes tests fragile and org-dependent. Always create test data explicitly."
What is the ALL ROWS keyword in SOQL? When do you use it?
ALL ROWS returns deleted records and records in the Recycle Bin along with active records. Use it to query recently deleted records or in after delete triggers to verify deletion.
// ALL ROWS — returns active + deleted (Recycle Bin) records
List<Account> allIncDeleted = [
SELECT Id, Name, IsDeleted
FROM Account
ALL ROWS
];
// Only deleted records
List<Account> deletedOnly = [
SELECT Id, Name FROM Account
WHERE IsDeleted = true
ALL ROWS
];
// Count including deleted
Integer totalCount = [SELECT COUNT() FROM Account ALL ROWS];
One-liner: "ALL ROWS includes soft-deleted records from the Recycle Bin — use it to query recently deleted records, in after delete triggers, or to count records including deleted ones."
Can you use WHERE clause on a field that is also in GROUP BY?
Yes — WHERE filters individual records before grouping. You can filter on any field in WHERE, including fields used in GROUP BY. HAVING then filters the aggregated groups after grouping.
// WHERE on GROUP BY field + HAVING on aggregate
List<AggregateResult> results = [
SELECT Industry, COUNT(Id) total
FROM Account
WHERE Industry != null // WHERE on same field as GROUP BY — valid!
AND CreatedDate = THIS_YEAR
GROUP BY Industry
HAVING COUNT(Id) > 3
];
One-liner: "You can filter on the same field in both WHERE and GROUP BY — WHERE pre-filters individual records, GROUP BY then groups what's left, HAVING filters those groups."
How do you create a Map from a SOQL query in one line?
Pass a SOQL query directly into the Map constructor — this creates an Id-to-sObject map in one line. Much cleaner than iterating and adding to a Map manually.
// One-line Map creation from SOQL
Map<Id, Account> accountMap = new Map<Id, Account>(
[SELECT Id, Name, Industry FROM Account]
);
// Use immediately
Account a = accountMap.get(someAccountId);
// Map with specific condition
Map<Id, Opportunity> openOpps = new Map<Id, Opportunity>(
[SELECT Id, Name, Amount FROM Opportunity WHERE IsClosed = false]
);
// Map keyed by non-Id field (manual loop needed)
Map<String, Account> byName = new Map<String, Account>();
for(Account a : [SELECT Id, Name FROM Account]) {
byName.put(a.Name, a);
}
One-liner: "new Map<Id, Account>([SELECT Id, Name FROM Account]) creates an Id-keyed map in one line — the Map constructor accepts a List<sObject> directly from SOQL."
How do you use SOSL RETURNING clause to filter and limit results per object?
SOSL RETURNING clause specifies which objects to search and which fields to return. You can add WHERE, ORDER BY, and LIMIT per object inside RETURNING.
// SOSL with RETURNING — filter, sort, limit per object
List<List<sObject>> results = [
FIND 'Acme' IN NAME FIELDS
RETURNING
Account(Id, Name, Industry WHERE Industry = 'Technology' ORDER BY Name LIMIT 5),
Contact(Id, Name, Email WHERE IsActive__c = true LIMIT 10),
Lead(Id, Name, Company LIMIT 5)
];
List<Account> accounts = (List<Account>) results[0];
List<Contact> contacts = (List<Contact>) results[1];
One-liner: "SOSL RETURNING allows per-object filtering with WHERE, sorting with ORDER BY, and row limits with LIMIT — results come back as List<List<sObject>> indexed by object position in RETURNING."
How does SOQL query size affect heap size? What happens when you query too many fields?
Each record queried consumes heap memory proportional to the number and size of fields returned. Querying unnecessary fields wastes heap. On the 6MB sync heap limit, a query with many Large Text Area fields can cause heap errors with just a few thousand records.
// BAD — queries all fields including large text areas
List<Case> cases = [SELECT Id, Subject, Description, /* all other fields */
FROM Case LIMIT 50000];
// Description is LongTextArea(32000) — 50K cases = heap overflow!
// GOOD — only query fields you actually need
List<Case> cases = [SELECT Id, Subject, Status, OwnerId FROM Case LIMIT 50000];
// For large text: query only when needed, process in smaller batches
for(Case c : [SELECT Id, Description FROM Case WHERE Description != null]) {
// SOQL for loop processes 200 at a time — safe for heap
}
One-liner: "Only SELECT fields you need — Large Text Area fields (up to 131,072 chars each) consume huge heap. Use SOQL for loop when processing records with big fields to avoid the 6MB heap limit."
How do you query FeedItem (Chatter posts) in SOQL?
Chatter posts are stored in FeedItem object. Query using standard SOQL. Use ParentId to get posts for a specific record. FeedComment stores replies to posts.
// Get Chatter posts for a specific Account
List<FeedItem> posts = [
SELECT Id, Body, CreatedById, CreatedDate, ParentId
FROM FeedItem
WHERE ParentId = :accountId
ORDER BY CreatedDate DESC
LIMIT 20
];
// Get comments on a post
List<FeedComment> comments = [
SELECT Id, CommentBody, CreatedById
FROM FeedComment
WHERE FeedItemId = :feedItemId
];
One-liner: "Chatter posts live in FeedItem — query with WHERE ParentId = recordId to get posts for any record. Replies are in FeedComment linked by FeedItemId."
How does Flow use SOQL? What are the governor limit implications?
Every Get Records element in Flow generates a SOQL query. Flows share the same governor limit pool with Apex in the same transaction. Flow-triggered by Apex inherits those limits.
Flow SOQL Best Practices
Use Get Records with filters to limit results. Avoid Get Records inside loops (Flow Loops). Use the Limit option to cap records. In record-triggered flows, avoid querying the same object that triggered the flow in a loop — use Assignment elements to build collections instead.One-liner: "Each Get Records element in Flow = one SOQL query toward the 100-limit. Flows triggered from Apex share the same limit pool. Never put Get Records inside a Flow Loop — same as never SOQL inside an Apex for loop."
What is Database.query() and when does it behave differently from static SOQL?
Database.query() executes a SOQL string at runtime. Key differences: it cannot use bind variables with colon syntax directly (use Database.queryWithBinds() instead), and any syntax error throws at runtime not compile time.
// Static SOQL — compile-time validation, bind vars work
String name = 'Acme';
List<Account> a = [SELECT Id FROM Account WHERE Name = :name]; // safe
// Database.query() — runtime execution, bind vars need workaround
String q = 'SELECT Id FROM Account WHERE Name = \''
+ String.escapeSingleQuotes(name) + '\'';
List<sObject> result = Database.query(q); // runtime error if bad syntax
// Database.queryWithBinds() — safe binds in dynamic SOQL (API v57+)
Map<String,Object> b = new Map<String,Object>{'n' => name};
List<sObject> safe = Database.queryWithBinds(
'SELECT Id FROM Account WHERE Name = :n', b, AccessLevel.USER_MODE);
One-liner: "Database.query() executes SOQL at runtime — errors appear at runtime not compile time. Use Database.queryWithBinds() for safe bind variables in dynamic SOQL."
Can you write SOQL without a FROM clause? What is [SELECT COUNT()] used for?
COUNT() without a field name can be used with a FROM clause but returns an Integer directly — not a List. It is used in Limits.getLimitQueries() checks and conditional logic before heavy queries.
// COUNT() returns Integer directly — no List needed
Integer accountCount = [SELECT COUNT() FROM Account];
Integer openOpps = [SELECT COUNT() FROM Opportunity WHERE IsClosed = false];
// Use before heavy processing
if([SELECT COUNT() FROM Account] > 50000) {
// Launch batch instead of inline processing
Database.executeBatch(new AccountBatch());
} else {
// Safe to process inline
}
// Also: Database.countQuery() for dynamic SOQL
Integer count = Database.countQuery('SELECT COUNT() FROM Account');
One-liner: "SELECT COUNT() FROM Object returns an Integer directly — no AggregateResult, no List. Use it for conditional logic before heavy processing or to check if record count exceeds a threshold."
How does Salesforce record sharing affect SOQL results?
SOQL results respect the running user's sharing settings by default when in a class declared with sharing. Without sharing classes bypass all sharing rules and return all records regardless of user access.
// WITH SHARING — SOQL respects OWD + sharing rules + manual shares
public with sharing class SecureQuery {
public List<Account> getAccounts() {
return [SELECT Id, Name FROM Account]; // only records user can access
}
}
// WITHOUT SHARING — SOQL returns ALL records, ignores sharing
public without sharing class AdminQuery {
public List<Account> getAllAccounts() {
return [SELECT Id, Name FROM Account]; // returns ALL accounts!
}
}
// INHERITED SHARING — inherits from calling context
public inherited sharing class FlexQuery { }
One-liner: "with sharing enforces record-level access in SOQL — users only get records they can see. without sharing bypasses all sharing and returns everything. inherited sharing takes the context from the caller."
How do you sort by multiple fields in SOQL?
List multiple fields in ORDER BY separated by commas. Each field can have its own ASC/DESC direction and NULLS FIRST/LAST setting.
// Multiple ORDER BY fields
List<Account> sorted = [
SELECT Id, Name, Industry, AnnualRevenue
FROM Account
ORDER BY
Industry ASC NULLS LAST, // Primary sort
AnnualRevenue DESC NULLS LAST, // Secondary sort
Name ASC // Tertiary sort
LIMIT 100
];
One-liner: "ORDER BY supports multiple fields comma-separated — each with its own ASC/DESC and NULLS LAST/FIRST. Primary sort is applied first, then secondary for ties, then tertiary."
Can you use Long Text Area fields in SOQL WHERE clause?
No. Long Text Area, Rich Text Area, and Encrypted fields cannot be used in WHERE, ORDER BY, or GROUP BY clauses. They can only be in the SELECT clause. Use SOSL instead if you need to search within Long Text Area content.
// VALID — LongTextArea in SELECT only
[SELECT Id, Description FROM Account] // Description is LongTextArea — OK in SELECT
// INVALID — LongTextArea in WHERE (throws error!)
// [SELECT Id FROM Account WHERE Description = 'Enterprise'] ERROR!
// ALTERNATIVE — use SOSL for text search in Long Text Area
List<List<sObject>> results = [FIND 'Enterprise' IN ALL FIELDS
RETURNING Account(Id, Name)];
One-liner: "Long Text Area, Rich Text, and Encrypted fields cannot be in WHERE, ORDER BY, or GROUP BY — SELECT only. Use SOSL to search within their content."
How do you query soft-deleted records in Salesforce?
Use ALL ROWS to include soft-deleted records in Recycle Bin. Filter with WHERE IsDeleted = true to get only deleted records. Standard SOQL without ALL ROWS never returns deleted records.
// Query deleted records from Recycle Bin
List<Account> deleted = [
SELECT Id, Name, IsDeleted
FROM Account
WHERE IsDeleted = true
ALL ROWS
];
// Undelete with ALL ROWS
List<Account> toRestore = [
SELECT Id FROM Account
WHERE IsDeleted = true
AND CreatedDate = TODAY
ALL ROWS
];
undelete toRestore;
One-liner: "ALL ROWS includes Recycle Bin records. Filter WHERE IsDeleted = true for deleted-only records. Use it before undelete operations to find what to restore."
Write a SOQL query to find the Account with the maximum number of Opportunities. Why can't you use MAX(COUNT())?
SOQL does not support nested aggregate functions like MAX(COUNT()). Use the workaround: COUNT per group → ORDER BY COUNT DESC → LIMIT 1 to get the top group.
// WRONG — nested aggregate not supported!
// [SELECT MAX(COUNT(Id)) FROM Opportunity GROUP BY AccountId] ERROR!
// RIGHT — COUNT + ORDER BY DESC + LIMIT 1
List<AggregateResult> top = [
SELECT AccountId, Account.Name, COUNT(Id) total
FROM Opportunity
GROUP BY AccountId, Account.Name
ORDER BY COUNT(Id) DESC
LIMIT 1
];
Id topAccId = (Id) top[0].get('AccountId');
Account acc = [SELECT Id, Name FROM Account WHERE Id = :topAccId];
One-liner: "SOQL doesn't support nested aggregates like MAX(COUNT()). Use COUNT(Id) with GROUP BY, ORDER BY COUNT(Id) DESC, LIMIT 1 to get the top group — then a second query for the full record."
How do you query the user who created a record and the user who last modified it?
Use CreatedBy.Name and LastModifiedBy.Name — these are standard Child-to-Parent traversals on the User object through the audit fields on every sObject.
[SELECT Id, Name,
CreatedDate, CreatedBy.Name, CreatedBy.Email,
LastModifiedDate, LastModifiedBy.Name,
Owner.Name, Owner.Email
FROM Account
WHERE CreatedDate = TODAY]
One-liner: "Every sObject has CreatedBy, LastModifiedBy, and Owner relationships — traverse with CreatedBy.Name, LastModifiedBy.Email etc. to get user details without a separate SOQL on User."
Can you use Formula fields in SOQL WHERE clause?
It depends on the formula field type. Simple formula fields (returning Text, Number, Date) can be used in WHERE. Formula fields with cross-object references or complex functions cannot be indexed and may cause non-selective queries.
// Simple formula field — works in WHERE
[SELECT Id FROM Opportunity WHERE DaysOpen__c > 30]
// Cross-object formula — may not be usable in WHERE
// Account.Owner.Profile.Name formula on Contact
// → Use the actual relationship traversal instead!
[SELECT Id FROM Contact WHERE Account.Owner.Profile.Name = 'Sales Rep']
// Formula fields cannot be sorted in ORDER BY in some cases
// If formula is non-deterministic — Salesforce may block it
One-liner: "Simple formula fields work in SOQL WHERE clause. Cross-object formula fields may not be filterable — use the underlying relationship traversal instead for better performance."
What is GROUP BY ROLLUP in SOQL? How is it different from GROUP BY?
GROUP BY ROLLUP generates subtotals and a grand total in addition to the individual group aggregates. It creates one row per group combination including progressively rolled-up totals — NULL in the result indicates a subtotal or grand total row.
// GROUP BY ROLLUP — gets subtotals per Industry AND grand total
List<AggregateResult> rollup = [
SELECT Industry, Type, COUNT(Id) total
FROM Account
GROUP BY ROLLUP(Industry, Type)
];
// Results include:
// Industry=Tech, Type=Customer → count for that combination
// Industry=Tech, Type=NULL → subtotal for all Tech accounts
// Industry=NULL, Type=NULL → grand total for all accounts
One-liner: "GROUP BY ROLLUP generates subtotals and grand totals automatically — NULL values in results indicate rollup rows. Use it for reports that need both detail rows and summary totals in one query."
When should you use SOQL vs Salesforce REST API for data retrieval?
Use SOQL inside Apex for server-side logic and triggers. Use REST API (with SOQL in query parameter) for external systems, integrations, or LWC server-side calls. REST API also supports SOQL via /query endpoint.
| Use Case | Approach |
|---|---|
| Apex trigger / class | Inline SOQL |
| LWC component data | @wire with Apex method |
| External system integration | REST API /query endpoint |
| Bulk data extraction | Bulk API 2.0 |
| Real-time streaming | Streaming API / Platform Events |
One-liner: "Use inline SOQL in Apex for server-side logic. Use REST API /query endpoint when external systems need to query Salesforce. Use Bulk API 2.0 for extracting millions of records externally."
How do you query files and attachments in Salesforce using SOQL?
Salesforce Files use ContentDocument, ContentVersion, and ContentDocumentLink objects. Attachments are the legacy system — ContentDocument is the modern approach for files.
// Get all files linked to a record
List<ContentDocumentLink> links = [
SELECT ContentDocumentId, ContentDocument.Title,
ContentDocument.FileExtension, ContentDocument.ContentSize
FROM ContentDocumentLink
WHERE LinkedEntityId = :recordId
];
// Get latest version of a file
List<ContentVersion> versions = [
SELECT Id, Title, VersionData, FileExtension
FROM ContentVersion
WHERE ContentDocumentId = :docId
AND IsLatest = true
];
One-liner: "Files live in ContentDocument, ContentVersion, ContentDocumentLink. Query ContentDocumentLink with LinkedEntityId = recordId to get all files attached to a specific record."
What is the difference between IsClosed and IsWon on Opportunity?
IsClosed = true for both Closed Won and Closed Lost. IsWon = true only for Closed Won. Use IsWon when you specifically need won deals, IsClosed when you need all completed deals regardless of outcome.
| StageName | IsClosed | IsWon |
|---|---|---|
| Prospecting | false | false |
| Negotiation | false | false |
| Closed Won | true | true |
| Closed Lost | true | false |
One-liner: "IsClosed = true means the Opportunity is done regardless of outcome (won or lost). IsWon = true means specifically won. Use IsWon for win-rate calculations."
How do you query when a record has multiple lookup fields to the same object?
When a record has two lookups to the same object (like two User lookups), each lookup has its own relationship name. Traverse them independently using their respective relationship names.
// Case has both ContactId and AccountId — two different lookups
[SELECT Id, Subject,
Contact.Name, Contact.Email, // Contact lookup
Account.Name, Account.Industry // Account lookup
FROM Case]
// Custom object with two User lookups
// Primary_Owner__c (relationship: Primary_Owner__r)
// Secondary_Owner__c (relationship: Secondary_Owner__r)
[SELECT Id, Name,
Primary_Owner__r.Name, // first User lookup
Secondary_Owner__r.Name // second User lookup
FROM Territory__c]
One-liner: "Multiple lookups to the same object use their own individual relationship names — traverse each one independently using dot notation. Custom fields use FieldName__r as the relationship."
🔐 Ready to Test Your Knowledge?
100 SOQL MCQ questions — timed, scored, with detailed explanations for every answer.
Pay once ₹299 — unlock all 22 premium quizzes instantly.
Pay once ₹299 — unlock all 22 premium quizzes instantly.
Expert Level SOQL
Q61 to Q80 — Questions for architect and lead developer roles
How do you use a SOQL result directly as a List in Apex without assigning to a variable?
You can use SOQL results directly in for loops, method calls, or expressions without storing in an intermediate variable — this is called inline SOQL and is cleaner for single-use queries.
// Inline SOQL in for loop
for(Account a : [SELECT Id, Name FROM Account LIMIT 10]) {
System.debug(a.Name);
}
// Inline SOQL in method call
processAccounts([SELECT Id, Name FROM Account WHERE Industry = 'Tech']);
// Inline SOQL to get first result
Account a = [SELECT Id, Name FROM Account LIMIT 1];
// Check size before accessing
List<Account> accs = [SELECT Id FROM Account WHERE Name = 'Acme'];
if(!accs.isEmpty()) { Account found = accs[0]; }
One-liner: "Inline SOQL can be used directly in for loops, method calls, or single-record assignments — no intermediate variable needed. Always check isEmpty() before accessing index [0] to avoid ListException."
What happens when you use WHERE Id IN with an empty Set in SOQL?
WHERE Id IN with an empty Set returns zero records — no error is thrown. This is safe and expected behavior. However, WHERE Id NOT IN with an empty Set returns ALL records — this is a common bug to watch for.
Set<Id> emptySet = new Set<Id>();
// IN with empty set — returns 0 records (safe)
List<Account> noResults = [SELECT Id FROM Account WHERE Id IN :emptySet];
System.debug(noResults.size()); // 0
// NOT IN with empty set — returns ALL records! (common bug)
List<Account> allAccounts = [SELECT Id FROM Account WHERE Id NOT IN :emptySet];
System.debug(allAccounts.size()); // All accounts in org!
// Always check before NOT IN queries
if(!excludeIds.isEmpty()) {
results = [SELECT Id FROM Account WHERE Id NOT IN :excludeIds];
} else {
results = [SELECT Id FROM Account];
}
One-liner: "WHERE Id IN with empty Set returns zero records safely. WHERE Id NOT IN with empty Set returns ALL records — always check if the exclusion Set is empty before using NOT IN."
What is WITH DATA CATEGORY in SOQL? When is it used?
WITH DATA CATEGORY filters Knowledge Articles and Answers by their data category. Used when querying Knowledge__kav, Question__kav, or other Knowledge objects that are organized by data category groups.
// Query Knowledge Articles by data category
List<Knowledge__kav> articles = [
SELECT Id, Title, Summary
FROM Knowledge__kav
WHERE PublishStatus = 'Online'
AND Language = 'en_US'
WITH DATA CATEGORY Product__c ABOVE_OR_AT Salesforce__c
];
// DATA CATEGORY operators:
// AT — exact category match
// ABOVE — parent categories only
// BELOW — child categories only
// ABOVE_OR_AT — category and all parents
One-liner: "WITH DATA CATEGORY filters Knowledge Articles by their assigned category hierarchy — use AT for exact match, ABOVE for parents, BELOW for children, ABOVE_OR_AT for category and all ancestors."
How do you check field-level security before running a SOQL query in Apex?
Use Schema.SObjectType.Account.fields.getMap() to get field describe results and check isAccessible() before including in SOQL. Or use WITH USER_MODE in Summer 26+ which handles this automatically.
// Manual FLS check before SOQL
Map<String, Schema.SObjectField> fieldMap =
Schema.SObjectType.Account.fields.getMap();
if(fieldMap.get('AnnualRevenue').getDescribe().isAccessible()) {
List<Account> accs = [SELECT Id, Name, AnnualRevenue FROM Account];
} else {
List<Account> accs = [SELECT Id, Name FROM Account];
}
// Summer 26+ — WITH USER_MODE handles this automatically
List<Account> accs = [SELECT Id, Name, AnnualRevenue FROM Account
WITH USER_MODE];
// Throws FieldAccessException if user can't see AnnualRevenue
One-liner: "Check FLS with Schema describe isAccessible() before querying sensitive fields manually. In Summer 26+, WITH USER_MODE handles this automatically — throws FieldAccessException if user lacks field access."
What is the difference between isAccessible(), isCreateable(), isUpdateable(), isDeletable() in Apex?
These Schema describe methods check CRUD permissions for the running user. Use them before DML and SOQL to enforce field and object-level security when WITH USER_MODE is not an option.
| Method | Checks | Use Before |
|---|---|---|
| isAccessible() | Read permission | SOQL SELECT |
| isCreateable() | Create permission | INSERT DML |
| isUpdateable() | Edit permission | UPDATE DML |
| isDeletable() | Delete permission | DELETE DML |
| isQueryable() | Can query object | Any SOQL FROM |
One-liner: "isAccessible() checks read permission before SOQL. isCreateable/isUpdateable/isDeletable check write permissions before DML. All are on Schema describe objects for both SObject and individual fields."
How do you use SOQL in Lightning Web Components?
LWC does not run SOQL directly — SOQL must be in an Apex method exposed with @AuraEnabled. Call it from LWC using @wire for reactive data or imperative Apex for on-demand calls.
// Apex class with SOQL
public with sharing class AccountController {
@AuraEnabled(cacheable=true)
public static List<Account> getAccounts(String industry) {
return [SELECT Id, Name, Industry
FROM Account
WHERE Industry = :industry
WITH USER_MODE
LIMIT 100];
}
}
// LWC — @wire for reactive
// import getAccounts from '@salesforce/apex/AccountController.getAccounts';
// @wire(getAccounts, { industry: '$industry' }) wiredAccounts;
// LWC — imperative for on-demand
// getAccounts({ industry: this.industry }).then(result => {
// this.accounts = result;
// });
One-liner: "LWC runs SOQL via @AuraEnabled Apex methods — use @wire for automatic reactive data binding or imperative calls for on-demand queries. Always mark methods cacheable=true for @wire to enable client-side caching."
What is the @wire adapter in LWC and how does it relate to SOQL?
@wire is a reactive LWC decorator that calls an Apex method or UI API adapter when component properties change. The Apex method runs SOQL server-side. Data is cached client-side when cacheable=true.
// Apex with cacheable=true — required for @wire
@AuraEnabled(cacheable=true)
public static List<Opportunity> getOpenOpps(Id accountId) {
return [SELECT Id, Name, Amount, StageName
FROM Opportunity
WHERE AccountId = :accountId
AND IsClosed = false
WITH USER_MODE];
}
// LWC JavaScript — @wire automatically re-calls when accountId changes
// @wire(getOpenOpps, { accountId: '$recordId' })
// wiredOpps({ data, error }) {
// if(data) this.opps = data;
// if(error) console.error(error);
// }
One-liner: "@wire calls cacheable=true Apex methods reactively when tracked properties change. SOQL runs server-side — results are cached in the LWC framework and returned from cache on subsequent calls."
What is Async SOQL and when would you use it?
Async SOQL (via Bulk API 2.0 or SOQL REST query with nextRecordsUrl) allows processing millions of records outside governor limits. It's for data exports, large reporting queries, and ETL operations that can't fit in a single synchronous transaction.
// REST API query — handles large results with queryMore
// GET /services/data/v60.0/query?q=SELECT+Id,Name+FROM+Account
// Response includes: {records: [...], nextRecordsUrl: '/...', done: false}
// Loop through pages
// while(!response.done) {
// response = GET(response.nextRecordsUrl);
// }
// Bulk API 2.0 — true async, no limits
// POST /services/data/v60.0/jobs/query
// Body: { operation: 'query', query: 'SELECT Id FROM Account' }
// Poll job status → download results as CSV
One-liner: "Async SOQL via Bulk API 2.0 runs outside all governor limits — use it for extracting millions of records, large data migrations, or ETL operations. REST API query handles pagination via nextRecordsUrl for moderate datasets."
What is the difference between a Lookup field name and its relationship name in SOQL?
The field name (AccountId) stores the Id value. The relationship name (Account) is used in SOQL to traverse to parent fields. For custom lookups, field = MyField__c, relationship = MyField__r.
// Standard lookup on Contact
// Field name: AccountId (stores the Id)
// Relationship name: Account (used to traverse parent)
[SELECT AccountId, Account.Name, Account.Industry FROM Contact]
// ^ field ^ traverse parent with relationship name
// Custom lookup on Order__c
// Field name: Customer__c (stores the Id)
// Relationship name: Customer__r
[SELECT Customer__c, Customer__r.Name, Customer__r.Email__c FROM Order__c]
// Parent-to-Child uses plural relationship name
[SELECT Name, (SELECT Id FROM Orders__r) FROM Customer__c]
One-liner: "Lookup field name (AccountId) stores the Id — relationship name (Account or Field__r) is used in SOQL to traverse to parent fields. Standard = no suffix, Custom lookup = __r suffix."
What is QueryMore (queryLocator.next()) and how is it used via REST API?
When a SOQL via REST API returns more than 2000 records, Salesforce paginates with nextRecordsUrl. Use the nextRecordsUrl from the response to fetch the next page until done = true.
// REST API paginated SOQL response
// {
// "totalSize": 50000,
// "done": false,
// "nextRecordsUrl": "/services/data/v60.0/query/01gxx...",
// "records": [...] // first 2000
// }
// Apex equivalent — Database.QueryLocator with iterator
Database.QueryLocator ql = Database.getQueryLocator(
'SELECT Id, Name FROM Account'
);
Database.QueryLocatorIterator it = ql.iterator();
while(it.hasNext()) {
Account a = (Account) it.next();
}
One-liner: "REST API paginates SOQL results at 2000 records per page using nextRecordsUrl — loop with GET requests until done=true to retrieve all records. Apex equivalent is Database.QueryLocatorIterator."
What is GROUP BY CUBE in SOQL?
GROUP BY CUBE generates aggregates for all possible combinations of the grouped fields — like a full cross-tabulation. It produces more subtotal rows than ROLLUP, covering every combination rather than just hierarchical subtotals.
// GROUP BY CUBE — all combinations of Industry + Type
List<AggregateResult> cube = [
SELECT Industry, Type, COUNT(Id) total
FROM Account
GROUP BY CUBE(Industry, Type)
];
// Results include ALL combinations:
// Industry=Tech, Type=Customer
// Industry=Tech, Type=NULL (subtotal for Tech all types)
// Industry=NULL, Type=Customer (subtotal for Customer all industries)
// Industry=NULL, Type=NULL (grand total)
One-liner: "GROUP BY CUBE generates subtotals for EVERY combination of grouped fields — more comprehensive than ROLLUP which only does hierarchical subtotals. NULL in results = a subtotal row for that dimension."
How do you handle SOQL in a transaction with mixed DML and queries? What is the mixed DML error?
Mixed DML error occurs when you try to DML on both setup objects (User, Profile, PermissionSet) and non-setup objects (Account, Contact) in the same transaction. Resolve by using @future method or System.runAs() in tests.
// Mixed DML error example
Account a = new Account(Name = 'Test');
insert a; // non-setup DML
User u = [SELECT Id FROM User LIMIT 1];
u.IsActive = true;
update u; // setup DML → MIXED DML ERROR!
// Fix — separate setup DML to @future
@future
public static void updateUser(Id userId) {
User u = [SELECT Id FROM User WHERE Id = :userId];
u.IsActive = true;
update u;
}
One-liner: "Mixed DML error: you can't DML setup objects (User, Profile) AND non-setup objects (Account) in the same transaction. Fix by separating setup DML into a @future method which runs in a new transaction."
How do you query Opportunities in multiple stages at once?
Use the IN operator with a Set or List of stage names. This is cleaner than multiple OR conditions and performs better.
// IN operator for multiple stages
Set<String> activeStages = new Set<String>{
'Prospecting', 'Qualification', 'Proposal/Price Quote', 'Negotiation'
};
List<Opportunity> active = [
SELECT Id, Name, StageName, Amount
FROM Opportunity
WHERE StageName IN :activeStages
ORDER BY Amount DESC
];
// Alternative — IsClosed for all closed stages
List<Opportunity> closed = [
SELECT Id, Name FROM Opportunity WHERE IsClosed = true
];
One-liner: "Use WHERE StageName IN :stageSet for multiple stages — cleaner than OR conditions and allows dynamic stage lists. Use IsClosed = true as a shortcut for all closed stages at once."
How do you query Activity (Task and Event) records in SOQL?
Task and Event are separate objects but also queryable through the Activity object (read-only, combines both). TaskRelation and EventRelation objects track who the activities are linked to when shared activities are enabled.
// Query Tasks for an Account
List<Task> tasks = [
SELECT Id, Subject, Status, ActivityDate, WhoId, WhatId
FROM Task
WHERE WhatId = :accountId
AND IsClosed = false
ORDER BY ActivityDate ASC
];
// Query Events for a Contact
List<Event> events = [
SELECT Id, Subject, StartDateTime, EndDateTime
FROM Event
WHERE WhoId = :contactId
AND StartDateTime >= :DateTime.now()
ORDER BY StartDateTime ASC
];
One-liner: "Tasks use ActivityDate, Events use StartDateTime/EndDateTime. WhoId links to Contact or Lead (polymorphic), WhatId links to Account, Opportunity, Case etc. Activity object combines both but is read-only."
What is the IsDeleted field in SOQL and when is it automatically set?
IsDeleted is a system field that Salesforce sets to true when a record is deleted but before it's purged from the Recycle Bin. Standard SOQL hides records where IsDeleted = true. Use ALL ROWS to see them.
// Standard SOQL — never returns IsDeleted = true records
List<Account> active = [SELECT Id, IsDeleted FROM Account];
// IsDeleted is always false here
// ALL ROWS — returns IsDeleted = true records (Recycle Bin)
List<Account> withDeleted = [SELECT Id, Name, IsDeleted FROM Account ALL ROWS];
// After delete trigger — check which records were cascaded
// Cascade-deleted child records have IsDeleted = true
List<Contact> cascadeDeleted = [
SELECT Id FROM Contact WHERE IsDeleted = true ALL ROWS
];
One-liner: "IsDeleted is set to true when a record goes to Recycle Bin. Standard SOQL never returns them — use ALL ROWS to include soft-deleted records and filter IsDeleted = true for deleted-only."
How do you query multi-select picklist fields in SOQL?
Multi-select picklist values are stored as semicolon-separated strings. Use INCLUDES() and EXCLUDES() operators in SOQL — standard = and IN operators don't work correctly for multi-select fields.
// Multi-select picklist — INCLUDES() and EXCLUDES()
List<Account> techAccounts = [
SELECT Id, Name, Products__c
FROM Account
WHERE Products__c INCLUDES('CRM;ERP') // has CRM OR ERP
];
// EXCLUDES — records that don't include these values
List<Account> noLegacy = [
SELECT Id FROM Account
WHERE Products__c EXCLUDES('Legacy System')
];
// INCLUDES with multiple — semicolon = OR, comma = AND
// INCLUDES('CRM;ERP') = has CRM OR ERP
// INCLUDES('CRM', 'ERP') = has CRM AND ERP
One-liner: "Multi-select picklists use INCLUDES() and EXCLUDES() in SOQL — not = or IN. Semicolon inside INCLUDES means OR, comma means AND. Standard operators don't work correctly on multi-select fields."
What causes a SOQL timeout and how do you fix it?
SOQL timeouts occur on non-selective queries doing full table scans on objects with millions of records. The CPU time limit (10s sync, 60s async) is reached. Fix by adding indexed field filters, custom indexes, or moving to Batch Apex.
Common Causes
Non-selective WHERE clause (no indexed field), OR conditions preventing index use, LIKE with leading wildcard (%text — can't use index), querying objects with millions of records without filters, large subqueries.// SLOW — leading % prevents index use
[SELECT Id FROM Account WHERE Name LIKE '%Corp'] // full scan!
// BETTER — trailing % can use index
[SELECT Id FROM Account WHERE Name LIKE 'Corp%'] // index used!
// BEST for text search — use SOSL instead
[FIND 'Corp' IN NAME FIELDS RETURNING Account(Id, Name)]
One-liner: "SOQL timeouts happen from non-selective full table scans. Fix by using indexed fields, avoiding leading % in LIKE, adding custom indexes via Support, or using SOSL for text search."
What is Database.countQuery() and when do you use it?
Database.countQuery() executes a Dynamic SOQL COUNT() query and returns the result as an Integer. Use it when you need to count records from a dynamically built query string.
// Static COUNT() — use when query is fixed
Integer count = [SELECT COUNT() FROM Account WHERE Industry = 'Tech'];
// Database.countQuery() — use when query is dynamic
String whereClause = 'Industry = \'' + String.escapeSingleQuotes(industry) + '\'';
Integer dynamicCount = Database.countQuery(
'SELECT COUNT() FROM Account WHERE ' + whereClause
);
// Common pattern — check before batch launch
Integer recordCount = Database.countQuery(
'SELECT COUNT() FROM Account WHERE CreatedDate = THIS_YEAR'
);
if(recordCount > 50000) {
Database.executeBatch(new AccountBatch(), 200);
}
One-liner: "Database.countQuery() executes a dynamic COUNT() and returns Integer — use it when you need to count records from a runtime-built query. Always check count before deciding whether to run inline or in Batch."
A SOQL query that worked in sandbox is slow in production with millions of records. How do you troubleshoot?
Sandbox has fewer records — non-selective queries pass fast. In production with millions, the same query does a full table scan. Use the Query Plan tool, check indexes, and add indexed field filters.
Troubleshooting Steps
1. Run Query Plan in Developer Console — check for TableScan and cost above 1.2. Check if WHERE fields are indexed (Id, Name, External IDs, Lookups).
3. Add CreatedDate or OwnerId filter to make query selective.
4. Request custom index on commonly filtered field via Salesforce Support.
5. Move to Batch Apex with Database.QueryLocator for large data processing.
// Add indexed field to make non-selective query selective
// Before: full table scan
[SELECT Id FROM Account WHERE Region__c = 'North']
// After: add indexed filter to help optimizer
[SELECT Id FROM Account
WHERE Region__c = 'North'
AND CreatedDate = LAST_N_YEARS:2] // CreatedDate indexed = selective
One-liner: "Sandbox → Production slowness means non-selective query. Run Query Plan to check — TableScan means no index. Add an indexed field to WHERE (CreatedDate, OwnerId, Name) or request a custom index via Salesforce Support."
What are the top 10 SOQL best practices every Salesforce developer must follow?
These 10 practices cover security, performance, and correctness — memorize them for any Salesforce interview.
| # | Practice | Why |
|---|---|---|
| 1 | Never SOQL inside a loop | Hits 100-query limit on bulk operations |
| 2 | Use bind variables (:var) | Prevents SOQL injection, cleaner code |
| 3 | Use WITH USER_MODE | Enforces FLS — required from Summer 26 |
| 4 | Always LIMIT when possible | Prevents unexpected 50K limit errors |
| 5 | Use indexed fields in WHERE | Keeps queries selective for large orgs |
| 6 | Use SOQL for loop for large data | Avoids 6MB heap size limit |
| 7 | Alias all aggregate functions | Prevents fragile expr0 auto-generated keys |
| 8 | Check isEmpty() before [0] | Prevents ListException on empty results |
| 9 | Check empty Set before NOT IN | Empty NOT IN returns all records! |
| 10 | Use Map from SOQL constructor | Clean, one-line Id-to-sObject map |
One-liner: "SOQL best practices: no SOQL in loops, bind variables for safety, WITH USER_MODE for FLS, LIMIT always, indexed WHERE fields, for loop for large data, alias aggregates, isEmpty() check, empty set check before NOT IN, Map constructor for clean lookups."
What is FOR VIEW and FOR REFERENCE in SOQL? How are they different from FOR UPDATE?
FOR VIEW and FOR REFERENCE update the Last Viewed Date and Last Referenced Date fields on records without locking them — unlike FOR UPDATE which locks records. Use them to track when records were accessed without preventing concurrent reads.
| Clause | Updates Field | Locks Record | Use Case |
|---|---|---|---|
| FOR VIEW | LastViewedDate | No | User viewed the record directly |
| FOR REFERENCE | LastReferencedDate | No | Record referenced from another record |
| FOR UPDATE | Nothing | Yes | Prevent concurrent modifications |
// FOR VIEW — updates LastViewedDate (user opened this record)
List<Account> viewed = [
SELECT Id, Name, LastViewedDate
FROM Account
WHERE Id = :accountId
FOR VIEW
];
// LastViewedDate is now updated to current timestamp
// FOR REFERENCE — updates LastReferencedDate (referenced from elsewhere)
List<Contact> referenced = [
SELECT Id, Name, LastReferencedDate
FROM Contact
WHERE AccountId = :accountId
FOR REFERENCE
];
// LastReferencedDate updated — record was referenced, not directly viewed
// Query recently viewed records
List<Account> recent = [
SELECT Id, Name, LastViewedDate
FROM Account
WHERE LastViewedDate != null
ORDER BY LastViewedDate DESC
LIMIT 10
];
Key Difference from FOR UPDATE
FOR VIEW and FOR REFERENCE do NOT lock records — multiple users can query with FOR VIEW simultaneously. FOR UPDATE locks the record row until the transaction commits. FOR VIEW/REFERENCE are tracking clauses, FOR UPDATE is a concurrency control clause.One-liner: "FOR VIEW updates LastViewedDate (user directly viewed), FOR REFERENCE updates LastReferencedDate (accessed indirectly). Neither locks the record — they are tracking clauses, not concurrency control like FOR UPDATE."
☕ Enjoyed this article?
SF Interview Pro is 100% free and maintained by a Salesforce professional. No ads, no paywalls, and no signup required. If this guide helped you prepare for an interview, earn a certification, or grow your Salesforce career, consider buying me a coffee! ☕💜
🇮🇳 UPI (India)
Pay by QR
GPay · PhonePe · Paytm · BHIM
New interview questions every week
Follow for fresh Salesforce Q&A, free courses, and real interview experiences — straight from the trenches.
Follow Us ↗