Accounts without Opportunities SOQL Query

Question

I have a requirement to query for master objects that have no corresponding detail objects. This would be similar to querying for Accounts with no Opportunities.

In SQL I might do this:

SELECT Id FROM Account A LEFT JOIN Opportunity O ON O.AccountID = A.Id WHERE O.AccountId IS NULL.

What is the SOQL way of doing this?

Answer 1

I would do the following query :

List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT AccountId from Opportunity)];

Updated:

List<TransactionHeader__c> transactions = [SELECT Account__c from TransactionHeader__c limit 10000];
List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT Account__c from TransactionHeader__c)];

Debug Log :

09:45:06.025 (25652305)|SOQL_EXECUTE_BEGIN|[1]|Aggregations:0|select Account__c from TransactionHeader__c limit 10000
09:45:06.130 (130013480)|SOQL_EXECUTE_END|[1]|Rows:10000
09:45:06.143 (143338699)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Id, Name from Account where Id NOT IN (select Account__c from TransactionHeader__c)
09:45:06.356 (356276543)|SOQL_EXECUTE_END|[2]|Rows:2774

Answer 2

If the relationship between the 2 objects is literally Master-Detail, you can add a rollup summary to the Master that counts the Details, then query for masters where the count is zero.

Answer 3

One of the option could be

Map<Id,Account> acctsWithOpps = new Map<Id,Account>([SELECT Id,(SELECT Id FROM Opportunities) FROM Account]);
System.debug(acctsWithOpps);

for (Account account : acctsWithOpps.values())
{
    if(account.Opportunities.size()==0)
    {
        System.debug(account + ' : ' + account.Opportunities);
    }
}