SOQL basics: SELECT, WHERE, and bind variables
SOQL (Salesforce Object Query Language) is a read-only query language purpose-built for retrieving sObject records, written directly inline in Apex inside square brackets, e.g. List<Account> accts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology']. Unlike SQL, SOQL always operates on a single object's fields and its related objects, has no SELECT *, and supports bind variables by referencing an Apex variable with a colon prefix, e.g. WHERE Industry = :industryFilter, which Salesforce automatically escapes to prevent SOQL injection.
Cricket analogy: A scorer asking the database 'give me every batter on this team with an average above 40' is exactly a SOQL WHERE clause, filtering the Contact-like records down to just the ones matching a condition.
// Basic SOQL with WHERE, ORDER BY, and LIMIT
List<Opportunity> topDeals = [
SELECT Id, Name, Amount, StageName, CloseDate
FROM Opportunity
WHERE StageName != 'Closed Lost' AND Amount > 50000
ORDER BY Amount DESC
LIMIT 10
];
// Bind variable usage (safe from SOQL injection)
String industryFilter = 'Technology';
List<Account> techAccounts = [
SELECT Id, Name FROM Account WHERE Industry = :industryFilter
];
// Relationship query: parent-to-child (subquery) and child-to-parent (dot notation)
List<Account> accountsWithContacts = [
SELECT Id, Name, Owner.Email,
(SELECT Id, FirstName, LastName FROM Contacts WHERE Email != null)
FROM Account
WHERE Id IN :techAccounts
];Relationship queries: parent-to-child and child-to-parent
SOQL traverses two relationship directions in a single query: child-to-parent uses dot notation on a lookup or master-detail field (Contact's Account.Name, or up to 5 levels deep like Opportunity.Account.Owner.Manager.Email), while parent-to-child uses a nested subquery referencing the child relationship name (Account's (SELECT Id FROM Contacts)), which returns a list you access per parent record. Custom object relationship names typically end in __r instead of the standard s, e.g. (SELECT Id FROM Invoices__r).
Cricket analogy: Looking up a bowler's captain's name (child-to-parent, dot notation: Player.Team.Captain__c) is different from a captain pulling up the full squad list under them (parent-to-child, a subquery of Players).
Child-to-parent dot-notation traversal can go up to 5 relationships deep in a single query (e.g. Case.Account.Owner.Manager.Email), but parent-to-child subqueries can only go one level per subquery — you cannot nest a grandchild subquery inside a child subquery in standard SOQL.
Aggregate functions and GROUP BY
SOQL supports aggregate functions — COUNT(), SUM(), AVG(), MIN(), MAX() — combined with GROUP BY to summarize data, returning AggregateResult records instead of sObjects. A query like SELECT StageName, SUM(Amount) total FROM Opportunity GROUP BY StageName produces one row per distinct StageName with the summed Amount, and HAVING further filters those grouped rows, e.g. HAVING SUM(Amount) > 100000, analogous to SQL's HAVING clause applying after aggregation rather than WHERE which applies before it.
Cricket analogy: A season summary showing total runs scored grouped by each batter, with a filter for only batters who crossed 500 runs, is exactly GROUP BY plus HAVING applied after the aggregation.
List<AggregateResult> pipelineByStage = [
SELECT StageName, COUNT(Id) numDeals, SUM(Amount) totalAmount
FROM Opportunity
WHERE IsClosed = false
GROUP BY StageName
HAVING SUM(Amount) > 100000
ORDER BY SUM(Amount) DESC
];
for (AggregateResult ar : pipelineByStage) {
System.debug(ar.get('StageName') + ': ' + ar.get('numDeals') + ' deals, $' + ar.get('totalAmount'));
}SOQL enforces a 50,000 total query rows returned per transaction and 100 queries per transaction. Selective, indexed WHERE clauses (on Id, external IDs, or fields with a custom index) matter enormously at scale — an unselective query on a multi-million-row object can throw a QueryException even before hitting row limits, because the query optimizer refuses a full table scan.
- SOQL is written inline in square brackets and always targets one primary object plus its related objects — there is no SELECT *.
- Bind variables (:variableName) safely inject Apex values into a query and are automatically escaped against SOQL injection.
- Child-to-parent traversal uses dot notation (up to 5 relationships deep); parent-to-child traversal uses a nested subquery on the relationship name.
- Custom object relationship names for parent-to-child subqueries end in __r instead of the standard s.
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY return AggregateResult rows instead of sObjects; HAVING filters after aggregation.
- Governor limits cap queries at 100 per transaction and 50,000 total rows returned per transaction.
- Selective, indexed WHERE clauses matter for performance and can be required by the query optimizer on large objects.
Practice what you learned
1. What is the correct way to safely inject an Apex variable's value into a SOQL WHERE clause?
2. How do you write a parent-to-child SOQL query to retrieve an Account along with all of its related Contacts in one query?
3. What does a SOQL query with GROUP BY return, instead of a List<sObject>?
4. What is the difference between WHERE and HAVING in a SOQL aggregate query?
5. For a custom object's child relationship in a parent-to-child subquery, what does the relationship name typically end in?
Was this page helpful?
You May Also Like
sObjects and DML
Understand Apex's sObject data model and the insert, update, upsert, and delete DML operations used to change Salesforce records, including bulk patterns and exception handling.
SOSL Searches
Learn Salesforce Object Search Language (SOSL) for full-text search across multiple objects and fields at once, and when to reach for it instead of SOQL.
Lists, Sets, and Maps
Master Apex's three core collection types — ordered Lists, unique Sets, and key-value Maps — and when to use each for bulk-safe, governor-limit-friendly code.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics