Using Joins in SOQL

Semi-Joins with IN:

Eg, query using objects - Account and Opportunity,

SELECT Id, Name

FROM Account

WHERE Id IN

( SELECT AccountId

FROM Opportunity

WHERE StageName = 'Closed Lost'

)

Anti-Joins with IN:

The following query returns account IDs for all accounts that do not have any open opportunities:

SELECT Id

FROM Account

WHERE Id NOT IN

(

SELECT AccountId

FROM Opportunity

WHERE IsClosed = false

)

Outer Joins where objects have child-to-parent relationship:

eg, all case records are returned whether they have an associated account or not,

SELECT Id, CaseNumber, Account.Id, Account.Name

FROM Case

ORDER BY Account.Name

eg, where the child-to-parent relationship is between custom objects:

SELECT ID, Name, Parent__r.id, Parent__r.name

FROM Child__c

ORDER BY Parent__r.name

Outer Joins where objects have parent-to-child relationship:

To get child records related to a parent record, add an inner query for the child records.

This example contains an inner query to get all contacts that are associated with each returned account.

SELECT Id, name, (select id, name from contacts)

FROM Account

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);

Outer Joins where custom objects have parent-to-child relationship

SELECT Id, name, (select id, name from customObject__r)

FROM ParentCustomObject