Records in Salesforce can be linked to each other through relationships: lookup relationships or master-detail relationships. For example, the Contact has a lookup relationship to Account. When you create or update a contact, you can associate it with an account. The contacts that are associated with the same account appear in a related list on the account’s page. In the same way you can view related records in the Salesforce user interface, you can query related records in SOQL.

To get child records related to a parent record, add an inner query for the child records. The FROM clause of the inner query runs against the relationship name, rather than a Salesforce object name. This example contains an inner query to get all contacts that are associated with each returned account. The FROM clause specifies the Contacts relationship, which is a default relationship on Account that links accounts and contacts.

SELECT Name, (SELECT LastName FROM Contacts) FROM Account WHERE Name = ‘SFDC Computing’

This next example embeds the example SOQL query in Apex and shows how to get the child records from the SOQL result by using the Contacts relationship name on the sObject.

Account[] acctsWithContacts = [SELECT Name, (SELECT FirstName,LastName FROM Contacts)
                               FROM Account 
                               WHERE Name = 'SFDC Computing'];
// Get child records
Contact[] cts = acctsWithContacts[0].Contacts;
System.debug('Name of first associated contact: ' 
             + cts[0].FirstName + ', ' + cts[0].LastName);

You can traverse a relationship from a child object (contact) to a field on its parent (Account.Name) by using dot notation. For example, the following Apex snippet queries contact records whose first name is Carol and is able to retrieve the name of Carol’s associated account by traversing the relationship between accounts and contacts.

Contact[] cts = [SELECT Account.Name FROM Contact 
                 WHERE FirstName = 'Carol' AND LastName='Ruiz'];
Contact carol = cts[0];
String acctName = carol.Account.Name;
System.debug('Carol\'s account name is ' + acctName);

These examples are based on standard objects. Custom objects can also be linked together by using custom relationships. Custom relationship names end with the __r suffix. For example, invoice statements are linked to line items through the Line_Items__r relationship on the Invoice_Statement__c custom object.

To get records for a:

• Child object, and include fields from a related parent object, use a child-to-parent query.
• Parent object, and include fields from a related child object, use a parent-to-child query.

In a child-to-parent query, you query the child object and get fields from a parent object by using dot notation, like this: SELECT Name, Account.Name FROM Contact

In a parent-to-child query, we use a subquery to get fields from the child object. A subquery is a SELECT statement enclosed in parentheses and nested within another query: SELECT Name, (SELECT Name FROM Contacts) FROM Account

Filtering with a Subquery:

SELECT Name, (SELECT Name FROM Contacts)
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = ‘Forbes’)

Learn more:

Relationship Queries with Standard Objects – Trailhead

Relationship Queries with Custom Objects – Trailhead

Salesforce Object Query Language – SOQL – Video series by Parikshith M. – Entire series recommended.