Inner Join and Outer Join

A SOQL statement consists of a single base object, specified using the FROM keyword. All fields in the base object can be retrieved in the query, as well as fields from parent and child objects depending on their distance away from the base object. Force.com takes care of joining related objects together to retrieve the requested fields.

These implicit joins are always outer joins. An outer join returns all records from the base object, including records that do not refer to a related object. To get a feel for this behavior, create a new Project record in the native user interface and leave all of its fields blank, but enter Test Project for the Name. Open Force.com IDE’s Schema Explorer and enter and run the query given in Listing 5.9.

Listing 5.9 SOQL Outer Join

Click here to view code image

SELECT Name, Account__r.Name
  FROM Project__c

This query returns the name and account name of the Projects. Account is the parent object of Project through a Lookup relationship. Because it is a Lookup relationship and not Master-Detail, it can contain a null value in Account__c, the Account foreign key field. With no foreign key to Account, Account__r, the foreign object reference, is also null.

You should see the five records imported from Listing 2.11 in Chapter 2, “Database Essentials,” plus the newly added record, named Test Project. Figure 5.1 shows the result of running the query. The Test Project record contains no value for Account__r yet was included in the results anyway. This is due to the outer join behavior.

Figure 5.1 Outer join results in Schema Explorer

In a relational database, this same query translated to SQL would result in five rows. The Test Project row would not be returned because it does not match a row in the Account table. Joins in SQL are inner by default, returning only rows that match both tables of the join.

To duplicate this inner join behavior in SOQL, simply add a filter condition to eliminate records without a matching record in the related object. For example, Listing 5.10 adds a filter condition to Listing 5.9 to exclude Project records without a corresponding Account.

Listing 5.10 SOQL Inner Join

Click here to view code image

SELECT Name, Account__r.Name
  FROM Project__c
  WHERE Account__c != null

The results of this query are shown in Figure 5.2. It has returned five records, each one with a corresponding parent Account record. The newly added Project record without the Account is correctly omitted.

Figure 5.2 Inner join results in Schema Explorer