SOQL self-join semi-join anti-join examples


Query Account from Contact using child-to-parent relationships:

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact

Sub Querying from Contact using parent-to-child relationships:

Select id, Name, Account.Name, Account.Owner.Name, Account.Owner.Profile.Name, RecordType.Name, 
(Select Opportunity.Name, Opportunity.StageName from Contact.OpportunityContactRoles), 
(Select id from Contact.Tasks), (Select Id from Contact.Cases), 
(Select Id from Contact.OpenActivities) from Contact

Outer Joins from Contact:

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where (Not Account.Owner.Profile.Name like 'Sales%')

Self-Join

Select c.id, c.Name, c.Account.Name, c.Account.Owner.Name,
c.Account.Owner.Profile.Name, c.RecordType.Name from Contact c 
where AccountId Not In (Select Id from Account 
where (Account.Owner.Profile.Name like 'Sales%'))

Semi-Join

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where Id In (Select ContactId from OpportunityContactRole
where Opportunity.StageName like 'Closed%')

Anti-Join

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact 
where Id Not In (Select ContactId from OpportunityContactRole where Opportunity.StageName like 'Closed%')

Combining Semi-Join and Anti-Join

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where Id In (Select ContactId from OpportunityContactRole where Opportunity.StageName like 'Closed%')
and AccountId Not In (Select Id from Account where RecordType.Name='Partner')
Advertisements

One Comment on “SOQL self-join semi-join anti-join examples

  1. Pingback: Force.com Apex and Visualforce Best Practices | Thys Michels Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: