Apex Soql Relationship Queries

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

Recommended Workflow

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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

• Dot-traversal depth does not exceed 5 levels in any chain
• Total relationship traversals across all chains in the query do not exceed 55
• Number of subqueries in parent-to-child query does not exceed 20
• getSObjects() called with the correct relationship name string (not the object API name)
• Explicit null check present before iterating the getSObjects() result
• Custom object relationships use __r suffix in both SOQL and getSObjects() call
• TYPEOF used for any polymorphic field; add an ELSE branch (optional per the SOQL reference) when unlisted object types must still return a value
• SOQL is outside all loops (bulkified)
• Query not routed through Bulk API if subqueries are present
• Any FROM-clause alias avoids SOQL reserved words (in, or, not, and the rest of the keyword list)

Salesforce-Specific Gotchas

1. 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;.
2. 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.
3. 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'.
4. Bulk API rejects parent-to-child subqueries — A query that works perfectly in synchronous Apex is rejected when the same query string is submitted to a Bulk API 2.0 query job. The documentation lists parent-to-child relationship queries among the unsupported constructs, alongside GROUP BY, OFFSET, TYPEOF, aggregate functions such as COUNT(), and compound address/geolocation fields. Child-to-parent traversal (Contact.Account.Name) is supported. Restructure any Bulk API code path as separate queries joined in your own code. Do not code against a specific named error string here: the rejection surfaces as a malformed-query/invalid-batch class failure, and the documentation does not publish a dedicated status code for it.
5. 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

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