SOQL Relationship Queries: Child-to-Parent and Parent-to-Child Examples
SOQL does not use arbitrary SQL joins. It traverses relationships already defined in the Salesforce data model.
Child to Parent
From Contact, Account is a parent. Use dot notation:
SELECT Id, FirstName, LastName,
Account.Id, Account.Name, Account.Industry
FROM Contact
WHERE Account.Industry = 'Technology'
The same pattern works through other parent relationships:
SELECT Id, Name, Owner.Name
FROM Opportunity
For custom relationships, use the relationship API name, commonly ending in __r:
SELECT Id, Name, Customer__r.Name
FROM Subscription__c
Parent to Child
From Account, there can be many Contacts, so use a nested query and the child relationship name:
SELECT Id, Name,
(SELECT Id, FirstName, LastName, Email
FROM Contacts)
FROM Account
You can filter the child query:
SELECT Id, Name,
(SELECT Id, Name, Amount
FROM Opportunities
WHERE IsClosed = false)
FROM Account
Relationship Name vs Object Name
A common mistake is assuming the child subquery always uses the object's API name. It uses the child relationship name defined by Salesforce metadata. For custom relationships, inspect the relationship metadata rather than guessing.
Relationship Queries Are Not Arbitrary Joins
You cannot decide at query time that two unrelated fields should be joined simply because their values happen to match. A Salesforce relationship must exist for relationship traversal.
If you need to return parents only when qualifying children exist, a semi-join can be a better fit than merely returning a child subquery.