Considering the following scenario:
- Child__c - Parent1__c (Lookup)
- Child__c - Parent2__c (Master-detail)
I want to query for Parent1 and Parent2 (Many Parent2 will be related to Parent1) and I want to keep the relationship between them (introduced through Child__c). Child__c is just a junction object in this case.
I have 2 options:
Select Child__c.Parent1__r, Child__c.Parent2__r from Child__c
Select Id, Name, ..., (Select Parent2__r.Id, Parent2__r.Name FROM Child__r) FROM Parent1__c
Because the relationship is not that simple, SOQL number 2 fits my purpose better.
However, I'm concerned of the performance of the nested SELECT statement.
So my main question is, are these cases optimized? This would be a regular JOIN in any SQL language.
Would I suffer any perofmance degradation in this case?
Any reason to prefer option 1 over option 2 or vice versa?