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