function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
krishna casukhela 7krishna casukhela 7 

clarification on inner join

Hello friends
I am trying to understand inner join , left outer , right outer , anti join on soql queries.
I have written two queries below, pleae let me know what type of joins they fall in, here's where I have confusion.

How to find all parent records with associated child records?
List<Account> accList=[select Account.Name,(select contact. Name from contacts)
                                   from Account
                                   where ID IN (select AccountID from Contact)];
 
I want to display those parent records who have orphaned children
List<Account> accList=[select ID, Name from Account
                                         where ID NOT IN (select AccountID from Opportunity
                                                                        where StageName='Closed Won')];

thanks
krishna
LBKLBK
I don't understand the part where you want to display parent records who have orphaned children. A child record is orphan because there is no parent for it.

Following SOQL query returns you all the Accounts and their Contacts.
SELECT Id, Name,(SELECT Id, Name FROM contacts) FROM Account WHERE ID IN (SELECT AccountID FROM Contact)
If you want all the Contacts, irrespective of they have an account or not, use the following query.
 
SELECT Id, Name, AccountId, Account.Name FROM Contact

If you need only the orphan contacts
 
SELECT Id, Name FROM Contact WHERE AccountId = null

Hope this helps.

 
krishna casukhela 7krishna casukhela 7
Hi
I only want to know the nature of joins used in soql

krishna
krishna casukhela 7krishna casukhela 7
Hi
also I am not displaying the records in visula force pgae. Just want to understand the type of join used and I know child record is orphan if no parent for it.
 
LBKLBK
If you keep your Child Object (for example, Contact) as the primary in the SOQL, you can fetch the orphan records as well. So this would be more like the OUTER join you are expecting.

If you use the Parent object (for example, Account) as the primary object in the SOQL query, it will act as INNER join and provide you only the children that has a parent.