The most popular API feature request we have ever gotten has been that we extend SOQL to allow it to return related objects along with the object being queried -- for example, to allow a single query to return a set of accounts and the set of related contacts and opportunities for each account.  I'm happy to say, we're delivering on this request with SOQL Relationships in Winter '07!

In Winter '07, the SOQL syntax has been extended to allow queries to traverse relationships and return related objects in a single API call.  The relationships are the ones that you have defined in the application via a Lookup Relationship or a Master-Detail relationship. 

The simplest case is where you traverse from queries target object to one or more of it's parent objects.  You can use parent (or many-to-one) relationships like this:

  SELECT Id, Name, StageName, Owner.Name, Account.Id, Account.Name
     FROM Opportunity WHERE Account.Industry = 'Banking'

This query uses parent relationships in two ways.  First, it filters Opportunities based on the industry of the parent Account in the WHERE clause.  Second, it returns data from parent objects -- the Name of the Opportunity's Owner, and the Id and Name of the parent Account.   Prior to relationships, this would have taken no fewer than three queries and some client side processing to produce equivalent results.

You can also use SOQL Relationships to return child objects too:

SELECT Id, Name, (SELECT Name, Phone FROM Contacts WHERE MailingState = 'CA') FROM Account

Notice the sub-query on Contacts in the field list?   This query of accounts returns the Id and Name of each account, and all the contacts  with MailingState equal to "CA" for each account.

Relationships vs. Joins

One frequent question is:  How are SOQL Relationships different than SQL JOINS?  SOQL Relationships have a lot in common with SQL Joins, but there are some key differences.

First, Relationships are defined in your data model, not in your queries.   The relationships you can use in queries are the ones you've defined between your objects in salesforce.com.   You don't need to define the relationship in the query itself.    We hope this makes the query syntax simpler and easier to master.   

Second, the results you get back are object hierarchies, not tables.   For example, if you ask for a set of accounts and all the child contacts for each of those accounts, you get back a set of accounts (one object per unique account) with the contacts for each account nested inside the account object.   

Finally, one thing that you can do with SQL JOINs that you cannot do with SOQL Relationships is exception reporting (i.e. find me all the accounts with no open opportunities).  SOQL can get you all the accounts and the contacts for each, but the client application will still be responsible for finding the "exception" accounts.    If you think this is an something SOQL needs, please vote for the idea over at the Idea Exchange.

For a more complete discussion of the syntax, how the results our returned, and some of the finer points of using Relationships, I invite you to check out our API 8.0 documents; the SOQL Relationships documentation is here.

I should also mentioned that we're also adding support for relationships to Apex Explorer 8.0 (fka sforce Explorer) -- look for it's release next week. Also, our new AJAX toolkit will support relationships right out of the box.

As you can probably see, this greatly increases the power of SOQL and our API.   One of our early developers reported being able to rebuild a custom detail page that previously required 7 queries with a single query and a lot less client-side code.  I look forward to hearing back from all of you -- please share your experiences and let us know what you think of this new functionality and syntax!

Aloha,

Pete