Recipes by Category

App Distribution (2) Bundle logic, interface and services for distribution. App Logic (37) The Apex programming language, workflow and formulas for logic. Collaboration (6) The Salesforce Chatter collaboration platform. Database (29) Data persistence, reporting and analytics. Integration (33) Web Service APIs and toolkits for integration. Security (9) Platform, application and data security. Tools (4) Force.com tooling User Interface (36) Visualforce MVC and metadata-drive user interfaces. Web Sites (12) Public web sites and apps with optional user registration and login.
Beta Feedback
Cookbook Home » Writing Shorter Queries Using Outer Joins

Writing Shorter Queries Using Outer Joins

Post by Developer Force  (2010-07-16)

Status: Certified
Level: novice

Problem

You'd like to write short, simple queries similar to an outer join in SQL. For example, “retrieve all the IDs for accounts whose opportunities are all closed.”

Solution

Use IN or NOT IN to write simple queries that exploit Salesforce.com support of semi-joins and anti-joins in SOQL.

For example, to find the account IDs for all accounts where there is a lost opportunity associated to the account, use a semi-join:

 SELECT Id, Name 
 FROM Account 
 WHERE Id IN 
(SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')

To find the account IDs for all accounts that have no open opportunities, use an anti-join query:

 SELECT Id 
 FROM Account
 WHERE Id NOT IN (SELECT AcountId FROM Opportunity
   WHERE IsClosed = false
 )

You can write nested queries using relationships. For example, to find opportunity IDs and their related line items if the line item value is greater than $10,000, issue a query similar to the following:

 SELECT Id, (SELECT Id from OpportunityLineItem)
 FROM Opportunity
 WHERE Id IN (
   SELECT OpportunityId FROM OpportunityLineItem
     WHERE totalPrice > 10000
 )

Discussion

Because semi-joins and anti-joins can potentially use a lot of resources during calculation, salesforce.com enforces some limits on these types of queries. For more information, see “Semi-Joins with IN and Anti-Joins with NOT IN” in the Web Services API Developer's Guide.

Share

Recipe Activity - Please Log in to write a comment

 inacloud - my apologies.  It wasn't intentional - but a bug in our (beta) Cookbook app!.

by Jon Mountjoy  (2011-01-19)

This is another useless post.  I hate to be critical but copy and pasting the same query over and over and you don't realize it makes me question the validity of all the jiber-jaber!

by inacloud  (2010-10-26)

X

Vote to Verify a Recipe

Verifying a recipe is a way to give feedback to others and broaden your own understanding of the capabilities on Force.com. When you verify a recipe, please make sure the code runs, and the functionality solves the articulated problem as expected.

Please make sure:
  • All the necessary pieces are mentioned
  • You have tested the recipe in practice
  • Have sent any suggestions for improvements to the author

Please Log in to verify a recipe

You have voted to verify this recipe.