A common requirement people need to execute on the Force.com platform is to perform a JOIN in SOQL. This creates a unique challenge to many new developers as JOIN is not supported the same way it is in SOQL. In fact, JOIN is not even a valid keyword for SOQL syntax! Frustrating for new folks, but fear not. You can get some JOIN functionality in SOQL queries using what is called SOQL Relationship Queries.
First, a little explanation why you can't perform JOIN function in SOQL. Force.com platform is cloud based and uses a Multi-Tenant architecture. A multi-tenant architecture means that multiple clients are all using the same computing resources. You are sharing the computing resources with other clients, and to prevent developers from impacting the performance of other clients there are some important rules or governor limits put in place. These are the Governor Limits so many people hear about when they get introduced to the platform.
Since JOIN queries can get resource intensive, SOQL puts some limits around them. Instead of being able to do a JOIN you can use a Relationship query to pull in related records. If you have a lookup field on a object, you can perform a Relationship Query on that field to execute one SOQL query and retreive the related records.
For example, Accounts and Contacts have a relationship. Contacts have a one to many relationship to accounts. To perform a Relationship Query to retrieve all Contacts for an Account, you can perform the following SOQL method:
List<Account> accounts = [Select Id, Name, (Select Id, FirstName, LastName from Account.Contacts) from Account limit 10];
Notice the Bold section I have highlighted in the query. That is the relationship query. Notice that is uses the Account.Contacts relationship to perform the query. You can use the Force.com IDE to browse the relationships on your parent objects to retrieve the names to use in the queries.
After executing this, you can iterate over the retrieved children records by accessing a List<SObject> on the parent records. For example, this full Apex code will execute the query, print each accounts name in the system log, and print each contact under each account:
List<Account> accounts = [Select Id, Name, (Select Id, FirstName, LastName from Account.Contacts) from Account limit 10];
For(Account account : accounts)
{
System.debug('Account Name: ' + account.Name);
For(Contact contact : account.Contacts)
{
System.debug('Contact Name is ' + contact.FirstName + ' ' + contact.LastName);
}
}
And now the output:
14:01:16.041 (41930000)|USER_DEBUG|[4]|DEBUG|Account Name: Acme
14:01:16.042 (42154000)|USER_DEBUG|[7]|DEBUG|Contact Name is Edward Stamos
14:01:16.042 (42272000)|USER_DEBUG|[7]|DEBUG|Contact Name is Howard Jones
14:01:16.042 (42433000)|USER_DEBUG|[7]|DEBUG|Contact Name is Leanne Tomlin
14:01:16.042 (42674000)|USER_DEBUG|[4]|DEBUG|Account Name: salesforce.com
14:01:16.042 (42845000)|USER_DEBUG|[7]|DEBUG|Contact Name is Marc Benioff
14:01:16.043 (43007000)|USER_DEBUG|[4]|DEBUG|Account Name: Global Media
14:01:16.043 (43121000)|USER_DEBUG|[7]|DEBUG|Contact Name is Geoff Minor
Pretty simple, right? You can execute several relationship queries in one query.
If you want more information, checkout the full documentation on the Force.com support website: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm
Stat Tracker
