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 (5) 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) 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


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.”


Use IN or NOT IN to write simple queries that exploit 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 
(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:

 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
   SELECT OpportunityId FROM OpportunityLineItem
     WHERE totalPrice > 10000


Because semi-joins and anti-joins can potentially use a lot of resources during calculation, 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.


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)


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 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.