+ Start a Discussion

SOQL to find Accounts with no contacts

I'd like to run a query to find all Accounts without Contacts. I'm not sure how to form the SOQL query to acheive that.


Can anyone make any suggestions?




I am not sure it works.

but try something simillar

select Id form Account where Not Id In (select AccountId form Contact) 


hope this may give you an idea. By the way i have not tried subqueries yet though 




That's almost right. The idea is correct, the syntax just needs a tweak


select Id,name from Account where Id not In (select AccountId from Contact) 


Good answers.



saved me 60 seconds just now - thanks. See 6 year old posts have value :)
Chris BairChris Bair
Saved me a lot more than 60 seconds. I was trying to find accounts without a case: select Id,name from Account here Id not In (SELECT AccountId FROM Case) 
Chris BairChris Bair
Sorry: left out a W
select Id,name from Account where Id not In (SELECT AccountId FROM Case)
Select Id,name from Account where Id not In (select AccountId from Contact) 
Check this it Will definitely work.
select Id from Account where Id not  In (select AccountId from Contact). 
I have already run this soql
Ankit Gupta SFDCLearnerAnkit Gupta SFDCLearner

For fetching records (account) who have no contacts plz refer below soql.

This code will run.
select Id, name from Account where Id In (select AccountId from Contact) 

if we are using not in mentioned in place of In, like the soql "select Id, name from Account where Id not In (select AccountId from Contact) "
than it will return all Accounts which have contacts.

Ajay K DubediAjay K Dubedi
Hi Benwrigley,

List<Contact> conList=new List<Contact>();
List<Account> accountList=new List<Account>();
conList=[Select AccountId From Contact Limit 10000];
Set<Id> idSet=new Set<Id>();
    for(Contact con:conList){
accountList=[Select Id,Name From Account where Id Not In:idSet Limit 10000];

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Ajay Dubedi
Ankit Gupta SFDCLearnerAnkit Gupta SFDCLearner
Just want to fetch no of parents records who have no child records, I need it's SOQL. Below soql is running for standard object SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity) I need it for custom object. Please try to solve.
krishna maharshikrishna maharshi
You can try this SOQL:
select name from account where id not in (select accountid from contact)
Dear all. these are all posts but I was wondering if anyone could give us a hand.. we do not have a developer and we are trying to remove all the accounts with no contacts related. We are a humanitarian not for profit. Thanks
Ishu ShuklaIshu Shukla
List<Account> results = [SELECT Id, Name, (Select Name from Contacts) FROM Account];
List<Account> accList = new List<Account>();
for(Account result : results){
    if(result.Contacts.size() == 0){

System.debug('Acc List'+accList);
Good Luck Guys.
select Id, Name from Account WHERE ID NOT IN (SELECT AccountId FROM Contact)