Relationship Query
A Relationship Query in Salesforce Object Query Language (SOQL) is a query that traverses object relationships to retrieve fields from related records in a single round trip.
Definition
A Relationship Query in Salesforce Object Query Language (SOQL) is a query that traverses object relationships to retrieve fields from related records in a single round trip. The query uses dot notation for child-to-parent traversals (Contact.Account.Name) and subqueries for parent-to-child traversals ((SELECT Id, Name FROM Contacts) FROM Account). The pattern is foundational to working with Salesforce data because almost every meaningful query involves multiple related objects, and constructing separate queries for each level of the relationship is inefficient and harder to maintain.
The Salesforce platform's relationship-aware SOQL is one of the features that distinguishes the platform from a generic SQL database. Relationships are first-class concepts in the data model, and the query language treats them as such. A single SOQL query can traverse up to five levels of child-to-parent relationships, can include any number of parent-to-child subqueries, and can mix the two patterns freely. Mastering relationship queries is essential to writing efficient Apex, building useful reports, and integrating with Salesforce through the API.
How relationship queries work in SOQL
Child-to-parent traversal with dot notation
The simplest relationship query is a child-to-parent traversal: SELECT Id, Name, Account.Name, Account.Industry FROM Contact. The query starts at Contact and traverses up to the related Account, returning the Account's Name and Industry alongside the Contact's own fields. The dot notation can continue traversing multiple levels: Contact.Account.Owner.Manager.Name reaches four levels up the hierarchy. SOQL supports up to five levels of child-to-parent traversal in a single query. Each traversal step uses the relationship name (Account, Owner, Manager) rather than the underlying field name (AccountId, OwnerId, ManagerId).
Parent-to-child traversal with subqueries
Parent-to-child traversals require a subquery: SELECT Id, Name, (SELECT Id, Email FROM Contacts) FROM Account. The query returns each Account along with a nested collection of its related Contacts. Subqueries can include their own WHERE clauses, ORDER BY clauses, and LIMIT clauses, providing significant control over which child records return. Multiple subqueries can appear in a single parent query, retrieving related Contacts and Opportunities and Cases all at once. Subqueries cannot themselves contain nested subqueries; each level of the hierarchy requires its own query.
Relationship names: custom versus standard
Relationship names follow conventions. Standard relationships use a singular name for parent reference (Account from Contact) and a plural name for child collection (Contacts from Account). Custom relationships derived from a custom Lookup field use the Lookup field's name with __r appended for parent traversal (Project__r) and __r appended to the child relationship name for collections (Project_Items__r). Getting the relationship name right is one of the most common sources of SOQL bugs; the relationship name field on the Lookup configuration shows the exact name to use. For custom objects, the child relationship name typically defaults to the parent's plural label and can be customized.
Filtering across relationships
WHERE clauses in SOQL can filter on related-record fields using the same dot notation. SELECT Id FROM Contact WHERE Account.Industry = 'Manufacturing' filters Contacts based on their Account's Industry. Multi-level filters are supported: WHERE Account.Owner.Profile.Name = 'System Administrator'. Subqueries can include their own WHERE clauses independent of the outer query, supporting patterns like SELECT Id, (SELECT Id FROM Contacts WHERE LastModifiedDate = LAST_WEEK) FROM Account. The flexibility lets a single query express complex multi-object filters that would otherwise require multiple queries and client-side joining.
Aggregate queries with relationships
Aggregate functions (COUNT, SUM, MIN, MAX, AVG) combine with relationship traversals to produce summary results across object boundaries. SELECT Account.Industry, COUNT(Id) FROM Contact GROUP BY Account.Industry returns Contact counts grouped by the related Account's Industry. Aggregate queries with relationships are useful for dashboard metrics and one-off analytics directly from the API or Developer Console. The same aggregations are also expressible through standard reports, but the SOQL form gives developers programmatic access to the same data.
Polymorphic relationships and the WhoId/WhatId pattern
Some Salesforce relationship fields are polymorphic: they can reference different parent objects depending on the record. Task and Event have WhoId (referencing Lead or Contact) and WhatId (referencing any of many objects). Polymorphic relationships in SOQL require the TYPEOF expression to handle each possible related object type. SELECT TYPEOF What WHEN Account THEN Name, Industry WHEN Opportunity THEN Name, Amount END FROM Task is the syntax for accessing fields specific to each possible WhatId type. Polymorphic queries are more complex but expressive enough to handle the variability in real Salesforce data.
Performance and query optimization
Relationship queries can be expensive if not designed carefully. Each level of child-to-parent traversal joins to another table at the database level; deep traversals (five levels) require careful indexing on the underlying foreign-key fields. Subqueries multiply the result set size: an Account query with three subqueries returns up to N Accounts times M Contacts times P Opportunities times Q Cases rows, which can grow quickly. The query optimizer in Salesforce handles most cases well, but very large queries or queries with very selective filters may hit performance issues. The Developer Console's Query Plan tool shows the optimizer's plan for a given query and identifies potential issues.
Common patterns and pitfalls in relationship queries
Across thousands of Salesforce Apex codebases, certain relationship query patterns recur with predictable outcomes. The N+1 anti-pattern: code that queries a list of parent records and then loops through them issuing a separate child query per parent. This pattern always produces poor performance, and the fix is always the same: refactor into a single query with a subquery so all the children come back in one round trip. The over-traversal anti-pattern: queries that traverse four or five relationship levels just to grab a single field. The fix is usually to denormalize: add a formula field on the closer object that exposes the deep value, or maintain a custom field via trigger to avoid the deep traversal. The over-fetching anti-pattern: queries that select every field on every related object even when only a few are needed. The fix is to be explicit about which fields the query returns rather than relying on SELECT * patterns from other languages. The under-filtering anti-pattern: queries without selective WHERE clauses that scan large tables. The fix is to add a selective filter or split the query into parts that can each filter selectively. Each pattern is recognizable once you have seen it, and code reviews that explicitly check for these patterns produce significantly better Apex over time. Salesforce includes some automatic protections through governor limits, but those limits catch the worst cases; the moderate cases that produce slow-but-functional code only get caught by code review or production monitoring.
Write effective relationship queries
Writing effective SOQL relationship queries combines understanding the data model with knowing the language syntax and the performance implications. The workflow below covers the standard sequence for developing and testing a complex relationship query.
- Identify the data the query needs to return
Document the question the query answers: what records to retrieve, what fields are needed from each, what filters apply. Identify the starting object (often the one with the primary filter), the parent objects to traverse upward (using dot notation), and the child collections to retrieve via subqueries. Sketch the relationship path before writing SOQL. Mistakes at this design step lead to inefficient queries that work but waste resources.
- Construct the query with relationship names
Write the SOQL using the correct relationship names. For standard objects, the relationship name typically matches the object name (Account from Contact). For custom relationships, use the relationship name shown on the Lookup field configuration (typically the field name with __r appended). Add WHERE clauses with the same dot notation for cross-object filtering. Add ORDER BY clauses for the outer query and any subqueries. Test the query in the Developer Console or Workbench against a small dataset first.
- Analyze the Query Plan for performance
For non-trivial queries, run the Query Plan tool in the Developer Console. The plan shows how the optimizer will execute the query, which indexes it will use, and whether any portion of the query will fall back to a table scan. Selective queries (those with WHERE clauses that filter to a small percentage of records using indexed fields) perform well; non-selective queries can produce timeouts on large datasets. Optimize by adding custom indexes, restructuring the query to start with the most selective filter, or breaking very complex queries into smaller pieces.
- Use the query in Apex or integration code
Once the SOQL is correct and efficient, embed it in the appropriate code path: an Apex class for internal logic, an external integration via the REST API or Bulk API, or a Visualforce or Lightning component for UI. For Apex, use bind variables to parameterize the query. For external integrations, use parameterized queries through the REST API to avoid string concatenation. Add Apex tests covering the query results against sample data. Monitor query execution time and limit consumption in production.
- Custom relationship names use __r, not __c. The Lookup field uses __c; the relationship reference uses __r in SOQL.
- Child-to-parent traversal is limited to five levels. Queries that need more levels must split into multiple queries.
- Subqueries cannot nest within other subqueries. Deeper hierarchies require multiple queries or different patterns.
- Polymorphic relationships require TYPEOF in modern SOQL. Older code may use awkward workarounds.
- Performance degrades on non-selective queries against very large objects. Always check the Query Plan for production-scale queries.
Trust & references
Cross-checked against the following references.
- Relationship Queries in SOQLSalesforce Developer Docs
Straight from the source - Salesforce's reference material on Relationship Query.
- SOQL ReferenceSalesforce Developer Docs
- Apex Developer GuideSalesforce Developer Docs
Hands-on resources to go deeper on Relationship Query.
About the Author
Dipojjal Chakrabarti is a B2C Solution Architect with 29 Salesforce certifications and over 13 years in the Salesforce ecosystem. He runs salesforcedictionary.com to help admins, developers, architects, and cert/interview candidates sharpen their fundamentals. More about Dipojjal.
Test your knowledge
Q1. What is a Relationship Query?
Q2. How is child-to-parent expressed?
Q3. Why use relationship queries?
Discussion
Loading discussion…