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?

3 Answers 3

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.

0
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)

6
  • great info, thanks @David! If I could mark both responses as accepted, I would! Commented Mar 3, 2018 at 1:18
  • Actually, this answer is incorrect in part. The subquery does not count towards the 50k limit.
    – sfdcfox
    Commented Mar 3, 2018 at 2:04
  • @sfdcfox, I tested it in a developer edition (see above) and the subquery is counted towards Limits.getQueryRows().
    – David Reed
    Commented Mar 3, 2018 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
    Commented Mar 3, 2018 at 2:39
  • @sfdcfox Indeed, I am a fool tonight! Thank you for educating me.
    – David Reed
    Commented Mar 3, 2018 at 2:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .