SOQL WHERE IN query

Question

Which of these is better:

 object__c[] objects = [SELECT name FROM object__c 
 WHERE opportunity__c IN 
 (SELECT OpportunityId From OpportunityLineItem WHERE Product2.Family = 'My Item')];

or:

 opportunityLineItem[] opIds = [SELECT OpportunityId FROM OpportunityLineItem WHERE Product2.family = 'My Item')];
 object__c[] objects = [SELECT name FROM object__c WHERE opportunity__c IN :opIds];

Basically, if the id is checking WHERE In an inner query does it run that inner query for each element returned, or does it just run it once and then use that value to check?

Answer 1

I would choose 1st Option.

object__c[] objects = [SELECT name FROM object__c 
 WHERE opportunity__c IN 
 (SELECT OpportunityId From OpportunityLineItem WHERE Product2.Family = 'My Item')];

There are 3 Reasons:

1) It will only use 1 SOQL Statement

2) It will not count Query rows in where clause inner query((SELECT OpportunityId From OpportunityLineItem WHERE Product2.Family = 'My Item'))

3) It will heap efficiently as you are not storing unnecessary records as compared to option 1.

Q? If the id is checking WHERE In an inner query does it run that inner query for each element returned, or does it just run it once and then use that value to check?

A) It runs only Once.

Answer 2

When it comes to theories, Pranay could have right, but every case, in my opinion, needs to be considered individually because we do not know any information about your organization.

I recommend using this tool:

Query Plan Tool

and if possible update your question with the data returned by the tool. Then we will see what is better.