Semi-Join and Anti-Join
In Chapter 4, “Business Logic,” you learned the two ways related objects can be included in SOQL: parent-to-child and child-to-parent queries. Semi-join and anti-join queries enhance the functionality of both queries, and add the ability to make child-to-child queries. In general, they allow records from one object to be filtered by a subquery against another object.
For example, suppose you need a list of all Account records that have at least one Project record in a yellow status. To make sure you have a valid test case, edit one of the Project records in the native user interface to set it to a yellow status. Try to write a query to return its Account, with Account as the base object.
You can’t do this without using a semi-join. Listing 5.11 shows one attempt. But it returns the unique identifiers and names of all Accounts and the unique identifiers of any Projects in yellow status. You would still have to write Apex code to filter through the Account records to ignore those without Project child records.
Listing 5.11 Parent-to-Child Query, Filter on Child
SELECT Id, Name,
(SELECT Id FROM Projects__r WHERE Status__c = 'Yellow')
FROM Account
Figure 5.3 shows the result of executing this query. Grand Hotels & Resorts Ltd is the Project in yellow status, and you can see that its Project record has been returned in the relationship field Projects__r.
Listing 5.12 rewrites this query using a semi-join. Read it from the bottom up. A subquery identifies Projects in yellow status, returning their Account unique identifiers. This set of Account unique identifiers is used to filter the Account records returned by the query. The result is a single Account, as shown in Figure 5.4.
Listing 5.12 SOQL with Semi-Join
SELECT Id, Name
FROM Account
WHERE Id IN
(SELECT Account__c FROM Project__c WHERE Status__c = 'Yellow')
An anti-join is the negative version of a semi-join. It uses the NOT IN keyword to allow the subquery to exclude records from the parent object. For example, Listing 5.13 returns all Accounts except those containing Projects in a green status. Note that the results include the Project in yellow status, as well as all Account records not associated with a Project.
Listing 5.13 SOQL with Anti-Join
SELECT Id, Name
FROM Account
WHERE Id NOT IN
(SELECT Account__c FROM Project__c WHERE Status__c = 'Green')
Returning to semi-joins, Listing 5.14 provides an example of another type, called child-to-child. It joins two child objects that aren’t directly related by relationship fields. The records in the Timecard object are filtered by contacts that have at least one assignment as a consultant. This means Timecards logged by contacts who are not assigned to a project as a consultant are excluded from the results. Child-to-child refers to the Timecard and Assignment objects, which are related to each other only in so much as they are children to other objects.
Listing 5.14 SOQL with Child-to-Child Semi-Join
SELECT Project__r.Name, Week_Ending__c, Total_Hours__c
FROM Timecard__c
WHERE Contact__c IN
(SELECT Contact__c FROM Assignment__c WHERE Role__c = 'Consultant')
Listing 5.15 demonstrates a third type of semi-join, the child-to-parent. Timecards are filtered again, this time to include consultants with an hourly cost rate of more than $100. Child-to-parent refers to the relationship between the Timecard and Contact objects. Contact is the parent object, and it is being used to restrict the output of the query on Timecard, the child object.
Listing 5.15 SOQL with Child-to-Parent Semi-Join
SELECT Project__r.Name, Week_Ending__c, Total_Hours__c
FROM Timecard__c
WHERE Contact__c IN
(SELECT Id FROM Contact WHERE Hourly_Cost_Rate__c > 100)
Several restrictions are placed on semi-join and anti-join queries:
The selected column in the subquery must be a primary or foreign key and cannot traverse relationships. It must be a direct field on the child object. For example, it would be invalid to rewrite the subquery in Listing 5.12 to return Account__r.Id in place of Account__c.
A single query can include at most two semi-joins or anti-joins.
Semi-joins and anti-joins cannot be nested within other semi-join and anti-join statements, and are not allowed in subqueries.
The parent object cannot be the same type as the child. This type of query can always be rewritten as a single query without a semi-join or an anti-join. For example, the invalid query SELECT Name FROM Project__c WHERE Id IN (SELECT Id FROM Project__c WHERE Status__c = 'Green') can be expressed without a subquery: SELECT Name FROM Project__c WHERE Status__c = 'Green'.
Subqueries cannot be nested and cannot contain the OR, count(), ORDER BY, or LIMIT keywords.