Semi Join, Anti Join Relationships in Salesforce

Semi Join and Anti Join Relationships in Salesforce are techniques in SOQL that allow you to filter records based on relationships between objects. They provide an efficient way to retrieve or exclude records based on specific criteria in related objects.

Semi Join

A Semi Join is used to filter records of one object based on related fields from another object before fetching those records. It retrieves records from the primary object if certain criteria are met in the related object.

Anti Join

An Anti Join performs the opposite of a semi join. It filters out records from the primary object based on their relationship with child objects specified through conditions. Essentially, it retrieves records from the primary object that have no corresponding records in the related object.

Note: Semi Joins and Anti Joins are exclusive to SOQL queries and provide an elegant solution for filtering records by relationships on large datasets.

Writing Semi Join and Anti Join in Salesforce

Semi join Query Example

Semi Join and Anti Join are used to to enhance the functionality of parent-to-child queries and child-to-parent queries in Salesforce Object Query Language and also they they are useful in create child-to-child relationships. These Semi Join and Anti Join allows records to filter from one object to another object using sub query.

Select Id, Name From Account WHERE Id IN (Select Account__c FROM Proj__c WHERE status__c = 'Yellow').

We have to read above SOQL query from the bottom to understand. Sub query identifies all projects which are in yellow status and also it returns their unique Id and Name.

SELECT Id, Name, Email
FROM Contact
WHERE Id IN (SELECT ContactId FROM Case)

SELECT Id, Name, Email: Specifies the fields from the Contact object to retrieve.
WHERE Id IN (SELECT ContactId FROM Case): The IN keyword operates as a semi join. It filters contacts whose Idmatches any ContactId in the Case object.

Anti join Query Example

SELECT Id, Name, Email
FROM Contact
WHERE Id NOT IN (SELECT ContactId FROM Case)

WHERE Id NOT IN (SELECT ContactId FROM Case): The NOT IN keyword operates as an anti join. It filters out contacts who have any cases associated with them

Select Id, Name FROM Account WHERE Id NOT IN (Select Account__c FROM Proj__c WHERE status__c = 'Green').

What are Semi Join and Anti Join Relationships in Salesforce? — Semi Join, Anti Join Relationships in Salesforce

Anti Join is Purely reverse to Semi Join. In Anti Join we use Key words like NOT, IN in sub queries to exclude certain records from the parent object. From above SOQL Query we are trying to retrieve all records from project object except the records which are in Yellow status and also we excluding the records which are not associated with parent object.

Examples of Semi Join and Anti Join in Practice

Semi Join Example

Retrieve contacts with active cases:

sqlCopy codeSELECT Id, Name, Email
FROM Contact
WHERE Id IN (
    SELECT ContactId
    FROM Case
    WHERE Status = 'Active'
)

Anti Join Example

Find customers who have not made any purchases:

sqlCopy codeSELECT Id, Name, Email
FROM Account
WHERE Id NOT IN (
    SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Won'
)

Conclusion

Semi Join: Retrieves records from the primary object that have related records in a secondary object.
Anti Join: Retrieves records from the primary object that have no related records in a secondary object.
Usage: Implemented in SOQL queries to efficiently fetch records based on relationships.
Benefits: Helps in writing complex queries, improves performance, and assists in data maintenance and reporting.

By understanding Semi Joins and Anti Joins, you can write more efficient and complex queries in Salesforce, allowing for better data analysis and reporting.