SOQL Relationship Queries in Apex
This skill activates when a practitioner needs to query related records across Salesforce objects — traversing parent fields with dot notation, pulling child records in a subquery, or handling polymorphic lookup fields like Task.WhatId. It covers correct SOQL syntax, Apex accessor patterns, and the hard platform limits that cause silent data loss when ignored.
Before Starting
Gather this context before working on anything in this domain:
- Confirm the relationship direction: are you reading parent field values from a child record (child-to-parent) or loading related child records from a parent (parent-to-child)?
- Check whether any lookup field is polymorphic. Standard polymorphic fields are
Task.WhatId, Task.WhoId, Event.WhatId, Event.WhoId, and FeedItem.ParentId. These require TYPEOF — a plain dot-notation WhatId.Name is not valid.
- Verify the API version. Parent-to-child subqueries are not supported in the Bulk API or for external objects. They require standard REST/SOAP API v58.0 or later.
- Know the relationship name: custom relationships use the
__r suffix (e.g. Custom_Object__r), standard relationships use the plural child name (e.g. Contacts, Opportunities).
Core Concepts
Child-to-Parent Dot Notation
A child record can access fields on its parent and grand-parent objects using dot notation in the SELECT clause or WHERE clause. Each dot step traverses one lookup or master-detail relationship upward.
SELECT Id, Name, Account.Name, Account.Owner.Name
FROM Contact
WHERE Account.Industry = 'Technology'
Hard limits (enforced at parse time):
- Maximum 5 levels of dot traversal in a single chain (e.g.
A.B.C.D.E.F is 5 hops — one more throws a parse error).
- Maximum 55 relationship traversals per query across all chains combined.
- Cross-object formula fields cannot be used in the
WHERE clause. Use the underlying field or traverse the relationship directly.
Alias Notation for Implicit-Join Filtering
SOQL supports alias notation in SELECT queries. You assign a short name to an object in the FROM clause and then reference that object — or a related object reached through it — by the alias everywhere else in the query. To establish an alias, name the object first and put the alias token immediately after it. To bring in a related parent object, add a comma and reference it through the base object's relationship path, then give it its own alias.
SELECT count()
FROM Contact c, c.Account a
WHERE a.Name = 'MyriadPubs'
Here Contact c aliases the base object and c.Account a aliases its related Account. This is an implicit join: it lets you filter on a parent record in WHERE without listing any parent field in the SELECT clause. Plain dot notation (WHERE Account.Name = 'MyriadPubs') resolves the same filter — alias notation is the documented alternative and reads more compactly when the same related object is referenced several times in one query.
Reserved words cannot be alias names. These SOQL keywords are rejected as alias identifiers: AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, USING, WHERE, WITH. Single letters (c, a) are safe, but avoid mnemonic short forms like in, or, and not — they collide with the reserved words and parse-error.
This FROM-clause object aliasing is a separate feature from aliasing a field or aggregate in the SELECT list (e.g. SELECT Name n, MAX(Amount) max FROM Opportunity GROUP BY Name), which is covered in apex-aggregate-queries.
Parent-to-Child Subqueries
A parent query can include a nested SELECT that retrieves all related child records. The inner SELECT references the child object by its child relationship name on the parent's object definition.
SELECT Id, Name,
(SELECT Id, LastName, Email FROM Contacts),
(SELECT Id, StageName FROM Opportunities WHERE StageName = 'Closed Won')
FROM Account
WHERE Type = 'Customer'
Hard limits:
- Maximum 20 subqueries per outer query.
- The outer query row limit is 50,000 records total (same as flat SOQL). Inner subquery rows count within that total.
ORDER BY inside subqueries is not supported in all API versions; prefer sorting in Apex if targeting older integrations.- Bulk API does not support subqueries. Any code path that runs these queries through the Bulk API will fail at runtime.
Accessing Child Records in Apex — getSObjects()
When a parent-to-child subquery returns results, the child list is not a typed List<SObject> you can cast directly. You must call getSObjects(relationshipName) on the parent SObject instance.
List<Account> accounts = [
SELECT Id, Name, (SELECT Id, LastName FROM Contacts)
FROM Account
];
for (Account acc : accounts) {
List<SObject> childRows = acc.getSObjects('Contacts');
if (childRows == null) {
continue; // No child records — getSObjects returns null, NOT an empty list
}
for (SObject row : childRows) {
Contact c = (Contact) row;
System.debug(c.LastName);
}
}
The relationship name string passed to getSObjects() is the child relationship name — same token used in the SOQL subquery. For custom objects it carries the __r suffix.
Polymorphic Fields and TYPEOF
Polymorphic lookups (Task.WhatId, Task.WhoId, Event.WhatId, Event.WhoId, FeedItem.ParentId) can reference records from multiple object types. The TYPEOF clause in SOQL lets you specify which fields to return depending on the concrete type of the referenced record.
SELECT Id, Subject,
TYPEOF WhatId
WHEN Account THEN Name, Industry
WHEN Opportunity THEN Name, StageName
ELSE Id
END
FROM Task
WHERE ActivityDate = TODAY
Key rules:
TYPEOF is required to project type-specific fields on a polymorphic lookup; plain dot notation like WhatId.Name is invalid.- The
ELSE branch handles any object types not listed in WHEN clauses.
TYPEOF has been generally available since API version 46.0 (Summer '19). The Developer Preview label of the SOQL Polymorphism feature applied only to API versions before 46.0 — on any currently supported version it is a stable, GA clause, so don't gate its use behind a "preview" caveat.TYPEOF is SELECT-clause only. It is rejected in WHERE, GROUP BY/HAVING, aggregate/COUNT() queries, Bulk API SOQL, Streaming API PushTopics, and the SELECT list of a semi-join subquery. To filter a polymorphic field by type in any of those contexts, use the .Type qualifier (see below).- In Apex, check
getSObjectType() (or use instanceof) on the referenced field value before casting.
Filtering a Polymorphic Field by Type (.Type)
Because TYPEOF is projection-only, the way to filter rows by the concrete type of a polymorphic field is the .Type qualifier. Type resolves to a plain string value ('Account', 'User', 'Opportunity'), so it compares with the ordinary string operators — =, !=, and, as the documented primary form, IN:
SELECT Id
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')
Rows whose reference resolves to a type outside the list are silently excluded — they are dropped from the result set, not returned with null fields. Per the docs, an Event pointing at a Campaign in What would simply not appear above. Keep this in mind when auditing polymorphic-field data completeness: a .Type IN (...) filter quietly narrows the population.
Once the filter pins the field to a single type, that type's own fields become addressable with ordinary dot notation:
SELECT Id, Owner.Name
FROM Event
WHERE Owner.Type = 'User'
Unlike TYPEOF, .Type filtering has no API-version floor and is the only legal way to select rows by polymorphic type inside the contexts where TYPEOF is banned — WHERE, Bulk API SOQL, semi-join inner queries, and GROUP BY/aggregate queries. The same .Type filter works verbatim from inside an Apex class; project the relationship with TYPEOF, then disambiguate the concrete type at runtime with instanceof before casting.
A field is polymorphic (and therefore eligible for .Type filtering) precisely when its describe metadata reports namePointing and polymorphicForeignKey as true with more than one entry in referenceTo.
Common Patterns
Pattern: Bulk-Safe Parent-to-Child with Null Guard
When to use: Trigger or batch handler that needs related child records for every parent in a collection.
How it works:
List<Account> accs = [
SELECT Id, Name,
(SELECT Id, Title FROM Contacts LIMIT 200)
FROM Account WHERE Id IN :accountIds
];
for (Account a : accs) {
List<SObject> contacts = a.getSObjects('Contacts');
if (contacts == null) continue; // explicit null guard is mandatory
for (SObject s : contacts) {
Contact c = (Contact) s;
// process c
}
}
Why not an alternative: Issuing a separate SOQL query per Account inside the loop burns one governor query per record. The subquery bundles all child data into a single round-trip.
Pattern: Selective Child Relationship Name for Custom Objects
When to use: Any time a custom object is the child side of a relationship.
How it works: Look up the child relationship name on the parent object's field definition in Setup > Object Manager > Fields & Relationships. The default is <ObjectPluralLabel>__r but the relationship name is configurable. Use that exact string in both the SOQL subquery and getSObjects().
-- Correct: custom child relationship name with __r
SELECT Id, (SELECT Id FROM My_Custom_Children__r) FROM Account
-- Wrong: using the object API name instead of the relationship name
SELECT Id, (SELECT Id FROM My_Custom_Child__c) FROM Account -- parse error
Decision Guidance
| Situation | Recommended Approach | Reason |
|---|
| Need parent field value on a child record | Child-to-parent dot notation in SELECT | Simple, single query, no extra round-trip |
| Filter on a parent object referenced repeatedly, no parent fields in SELECT | Alias notation (FROM Contact c, c.Account a) or plain dot notation | Both filter without selecting parent fields; the alias gives the object a compact handle for repeated references |
| Need all related child records for a set of parents | Parent-to-child subquery with getSObjects() | One query, avoids N+1 SOQL problem |
| Need to project per-type fields off a polymorphic lookup | TYPEOF ... WHEN ... END in the SELECT clause | Only clause that returns different fields per referenced type |
Need to filter rows by polymorphic type (WHERE, Bulk API, aggregate, semi-join) | .Type qualifier, e.g. What.Type IN ('Account','Opportunity') | TYPEOF is SELECT-only; .Type is the only legal filter and has no API-version floor |
| Running query through Bulk API | Separate queries, no subqueries | Bulk API rejects relationship subqueries at runtime |
| More than 20 child object types needed | Break into multiple queries by object | Hard 20-subquery limit per outer query |
| Need child records sorted for UI display | Sort in Apex after getSObjects() | ORDER BY in subquery has inconsistent API-version support |
Recommended Workflow
- Identify relationship direction and type. Determine whether you need child-to-parent traversal, a parent-to-child subquery, or both. Note whether any field is polymorphic. Confirm the exact relationship names from Setup or
Schema.DescribeFieldResult.
- Verify limits before writing the query. Count dot-traversal depth (max 5) and total traversals (max 55) for child-to-parent. Count subqueries (max 20) for parent-to-child. If limits are tight, split into multiple queries and merge results in Apex.
- Write the SOQL. Use correct relationship name tokens: plural child relationship name for standard objects (
Contacts, Opportunities), __r suffix for custom objects. Add TYPEOF with WHEN/ELSE for any polymorphic field.
- Access child records safely in Apex. Call
getSObjects(relationshipName) — never cast the relationship result directly. Add an explicit null check before iterating because getSObjects returns null when no child records exist for a row.
- Bulkify. Place SOQL outside loops. Pass a
Set<Id> via :bindVariable in the WHERE clause. Limit the inner subquery row count with LIMIT if the child volume per parent can be very large.
- Test boundary conditions. Write unit tests with zero children, one child, and many children per parent. Confirm no
NullPointerException from the missing null guard. Use @isTest(SeeAllData=false) and create test data explicitly.
- Validate governor usage. Use
Limits.getQueries() before and after to confirm the query count is as expected. Assert in tests that no extra SOQL is issued inside loops.
Review Checklist
Salesforce-Specific Gotchas
- getSObjects() returns null, not an empty list — When a parent record has no related children,
acc.getSObjects('Contacts') returns null. Iterating null in a for loop throws a NullPointerException at runtime. Always guard with if (childRows == null) continue;.
- Custom relationship name vs object API name — Using
My_Custom_Child__c (the object API name) instead of My_Custom_Children__r (the child relationship name) in a subquery causes a compile-time parse error. The relationship name is set on the lookup/master-detail field definition and may differ from the object name.
- Cross-object formula fields are not filterable — A formula field that references a parent field (e.g.
Account_Industry__c as a formula on Contact) cannot be used in a WHERE clause. Use the direct dot-notation traversal instead: Account.Industry = 'Technology'.
- Bulk API rejects subqueries — Code that works perfectly in synchronous Apex will throw a
QUERY_WITH_SELECTIVITY_HINT_ONLY_ALLOWED_IN_SUBQUERY or similar runtime error when the same query string is executed through the Bulk API. Remove subqueries and restructure as separate queries for any Bulk API code path.
- ORDER BY inside subqueries is unreliable across API versions — Sorting a subquery result is not guaranteed across all Salesforce API versions. Sort in Apex after calling
getSObjects() if ordering matters.
Output Artifacts
| Artifact | Description |
|---|
| SOQL query string | Relationship query ready for inline or Database.query() use |
| Apex loop block | Null-guarded getSObjects() iteration pattern |
| TYPEOF clause | Polymorphic field handler with the needed WHEN branches and an optional ELSE catch-all |
| Alias-notation query | FROM-clause object aliases for implicit-join parent filtering |
Related Skills
- apex-aggregate-queries — Use for GROUP BY, COUNT, SUM, AVG, and HAVING clauses; relationship subqueries and aggregate queries are mutually exclusive in the same query
- apex-soql-fundamentals — Use for foundational SELECT syntax, WHERE filters, ORDER BY, LIMIT, and OFFSET before layering relationship traversal
- apex-dml-patterns — Use when the relationship query results drive insert/update/delete operations
- apex-batch-chaining — Use when relationship query result volume requires chunked Batch Apex processing
