SOQL Queries

Analogy categoryAnalogies

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.

apex

// 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.

apex

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.