My requirement is to find the duplicate lead for the current lead by matching against all other leads in the database. something like this:


I am not sure how the same can be done using SOQL. Can somebody help me on this?


Just to clear the confusion. I am not looking for a way to prevent duplicates in the system. My goal is to find duplicate leads for a given lead in a batch job. I am going to use this query in the batch job execute method.

  • Dear downvoter, This is the second question you downvoted in two days, not sure what is your problem.
    – javanoob
    Sep 1, 2015 at 10:47

1 Answer 1


The example you provided

INNER JOIN Leads SecondTable ON SecondTable.FirstName = firstTable.FirstName AND SecondTable.LastName = FirstTable.LastName AND SecondTable.Email = FirstTable.Email

is a match on all three fields so that is easy....

  1. Make an external ID field that concatonates the First and lastName fields along with the email and set it to a unique index. Name it say dupIdx

Then in the lead trigger it would be something like:

trigger findDuplicates on lead(after Insert){

   Map<String,Lead[]> duplicates = New Map<String[]>();

   for(trigger.new l : lead){
           duplicates.get(l.dupIdx__c).add(l); //catches dupes in the trigger
           dulicates.put(l.dupIdx__c,New lead[]);

   for(Lead l : [Select ID, dupIdx__c From Lead Where dupIdx__c IN :duplicates.keyset() AND ID NOT IN :trigger.new]){

   //now run through trigger again checking for the dupes and add errors as appropriate


If you want to then prevent duplicates, you can make the field unique and case insensitive and then you do not need the trigger at all.

  • Can I do this without creating an extra field on the lead object? I want to do this for selected set of leads in a scheduled batch.
    – javanoob
    Aug 14, 2015 at 20:38
  • Actually I want to use this query in this batch job. Here is my other question salesforce.stackexchange.com/questions/88105/…
    – javanoob
    Aug 14, 2015 at 20:41
  • I take the time to answer your question only to find out that your question is not really the question you wanted to ask....???...And yes you could do this in a batch. Yes you could do it without the field but it introduces unnecessary complexity.....
    – Eric
    Aug 14, 2015 at 20:53
  • No Eric..I am really sorry..I am still looking for query to get duplicate leads but I just don't want to create extra field on the lead object...My sincere apologies for any miscommunication
    – javanoob
    Aug 14, 2015 at 20:54
  • Why would you have a problem creating the field? it takes A LOT of the work off of the code with the field present. Otherwise you will have to query the entire database and evaluate OR set a scope size of 1 in the batch and perform a query for each lead.....
    – Eric
    Aug 14, 2015 at 20:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .