Retrieve accounts that don't have any open opportunities

Question

I want to write a query to retrieve accounts that don't have any open opportunities.

I was looking at nested queries, so I wanted to run something like this:

Select id from Account WHERE id  = (SELECT AccountId from Opportunity where IsClosed = false)

Answer 1

You need to write a query with IN for receiving account id list from your nested query SELECT AccountId from Opportunity where IsClosed = false.

So your query will be followingly,

SELECT Id FROM Account WHERE Id IN (SELECT AccountId from Opportunity WHERE IsClosed = false)

And refer this link to learn more about SOQL queries.

Answer 2

You may want NOT IN in this case; this will find any accounts with no open opportunities (including those with no opportunities at all):

Select id from Account WHERE Id NOT IN (
  SELECT AccountId from Opportunity where IsClosed = true
)