0

I had never tried this before but another developer passed it along as a method to refine a SOQL query to find Accounts that didn't have Opptys:

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

Namely, my question is can I continue to use the nested SELECT query to refine lists as I did above?

This method worked great but I'm afraid to use it in a trigger as I feel like it may hit SOQL limits?

1

If you have a LDV (Large Data Volume), which kicks in around 1,000,000 rows of data (for standard objects), then your query may start to fail with non-selective query errors. However, as long as your other filters are selective enough, this might not be a problem. Keep in mind that "OR" is not allowed with sub-select semi-joins as well, so if you need to combine OR values, then you'll also be limited in that manner. Those potential issues aside, you can certainly use this method.

1

Just a thought, as an optimization. Opportunity is a Master-Detail object of Account. You can create a standard Rollup Summary Field on Account equal to the count of Opportunities. Now your SOQL can just filter on the rollup field being 0. Much less expensive query.

1

It's a perfectly legitimate SOQL query, but yes, you do run the risk of encountering SOQL limits, and you should evaluate those risks against the total data volume that you presently have and reasonably project in your org.

This query could be in danger of hitting the SOQL row limit of 50,000 rows; depending on the total data volume, you could also risk selectivity exceptions, which kick in in a trigger context at 200,000 total records in an object table.

(thank you to @sfdcfox for correcting my error regarding the SOQL row limit, which does not aggregate the count from the semi-join as it does with subqueries)

  • great info, thanks @David! If I could mark both responses as accepted, I would! – sfdcJameson Mar 3 '18 at 1:18
  • Actually, this answer is incorrect in part. The subquery does not count towards the 50k limit. – sfdcfox Mar 3 '18 at 2:04
  • @sfdcfox, I tested it in a developer edition (see above) and the subquery is counted towards Limits.getQueryRows(). – David Reed Mar 3 '18 at 2:23
  • @DavidReed You did the wrong query. sub-query rows count, but semi-join queries do not. Try it with select id from account where id not in (select accountid from contact). – sfdcfox Mar 3 '18 at 2:39
  • @sfdcfox Indeed, I am a fool tonight! Thank you for educating me. – David Reed Mar 3 '18 at 2:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.