How to modify semi-join and anti-joins

Question

I have similar code to this where I want to store the values of a query from a semi-join and anti-join. I want to store the values in a list respective on whether if its a semi-join or anti-join. But since both of them are nearly identical I don't want to call both at them twice.

Original

List<Account> inQuery = [SELECT Id, Name
FROM Account
WHERE Id IN
(SELECT AccountId
FROM Opportunity
WHERE StageName = 'Closed Lost')]

List<Account> notQuery = [SELECT Id, Name
FROM Account
WHERE Id NOT IN
(SELECT AccountId
FROM Opportunity
WHERE StageName = 'Closed Lost')]

// do something with inQuery

// do something with notQuery

I want to do the inner query once and add the values into each respective list. I was thinking of something like this but I cant figure out how to do it or if there is another way to approach it.

Modified

List<Opportunity> innerQuery = [SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost'];

List<Account> inQuery = new List<Account>();

List<Account> notQuery = new List<Account>();

for (Account acc: [SELECT Id, Name FROM Account]) {

   if() { // if some statement is true, then add to inQuery

   }
   else { // else add to notQuery

  }

}

// do something with inQuery

// do something with notQuery

Answer 1

I think you're approaching the query from the opposite direction. For your script you will need a query that returns all accounts. The difference you want to make is based on whether or not the account has an opportunity that's closed lost. So add a subquery to your account query that selects all opportunities of that account that are closed lost;

List<Account> accts = [
    SELECT Id, 
        (SELECT Id FROM Opportunities WHERE StageName = 'Closed Lost') 
    FROM Account
];

Then, in the loop, check if there are any opportunities returned, in which case, you want to do your "inQuery" logic. If the list is empty, that means there are no Closed Lost opportunities for that account, you apply your "notQuery" logic;

for(Account acct : accts){
    if(acct.Opportunities.size() > 0){
        //inQuery logic
    } else {
        //notQuery logic
    }
}

One major thing to notice here though is that you have to keep the Salesforce limits into account. Not only can you only query op to 50,000 records in one context, you can update up to 10,000. So as soon as you have more than 10,000 accounts in your database this script will fail. You will likely need a batch (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm) or a different approach. You could for instance use the dataloader to apply logic to all existing accounts and apply the logic for the accounts with a Closed Lost opportunity in a trigger on opportunity.