A deeper look at SOQL and Relationship Queries

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.

Advertisements

2 comments

  1. This is great, i really appreciate this for clearing me on few concepts on how to query data in Salesforce.It will be great if we can have more elaborative examples. thanks a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s