Click here to refer basics of Relationship Query
SOQL is the object oriented language for querying data in Force.com platform. You can look at it as an object oriented version of SQL. Lets start by examining the kind of relationships that are available. There are two kinds of relationships Lookup Relationship and Master-Detail Relationship.
Lookup Relationship
1) Deletion of parent record does not effect child record. This does not support roll up summary.
2) Sharing rules of parent does not apply to child. This allow us to have more flexibility in building security model.
3) A relationship doesn’t have to exit in this model. To put it in more simple terms, the lookup field doesn’t need to be populated.
Master-Detail Relationship
1) Deletion of parent record results in cascade deletion. This relationship supports roll up summary.
2) Sharing rules of parent does apply to child.
3) Unlike lookup relationship child record cannot exist on its own. Again in simple terms, the lookup field needs to be populated.
Now lets dive deep in to the relationship queries. I am using the following relationship:
SOQL Joins
Right Outer Join
A Right Outer Join takes all the record from the right table and then pick up any matching records from the left table.
Select Name, Position__r.Department__c From Job_Application__c
This query will return job application along with related departments
we used Position__r.Department__c to create a Right join between Job_Application__c and Position__c.
Left Outer Join
A Left Outer Join takes all the records from the left table and pick up any matching records from the right.
Select Name, (Select Name From Job_Applications__r) From Position__c
This will return all position with their related list of applications
Left Inner Join
A Left Inner Join display records only when there is a match between both tables. It takes records from the left table and pick the matching records from the right.
Select Name From Position__c Where Id IN (Select Position__c From Job_Application__c)
This will return positions for which there are associated applications
Right Inner Join
A Left Inner Join display records only when there is a match between both tables. It takes records from the right table and pick the matching records from the right.
Select Name, Position__r.Name From Job_Application__c Where Position__r.Department__c=’Sales’
This returns all applications for positions that are in ‘Sales’ department.
Left Anti Join
This is opposite to Left Inner Join.
Select Name From Position__c Where Id NOT IN (Select Position__c From Job_Application__c)
This returns name of all positions which have no job applications.
Right Anti Join
This is opposite to Right Inner Join.
Select Name From Job_Application__c Where Position__c=null
This returns name of all applications for which there are no associated positions.