Click here to Skip to main content
15,883,692 members
Articles / Programming Languages / SQL

How Can I Find Duplicate Values in SQL Server?

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
4 Jan 2016MIT6 min read 17.1K   3   3
How to find duplicate values in SQL Server

In this article, find out how to find duplicate values in a table or view using SQL. We’ll go step by step through the process. We’ll start with a simple problem, slowly build up the SQL, until we achieve the end result.

By the end, you’ll understand the pattern used to identify duplicate values and be able to use it in your database.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

Find Duplicate Values in SQL Server

Let’s get started. We’ll base this article on a real-world request; the human resource manager would like you to find all employees sharing the same birthday. She would like the list sorted by BirthDate and EmployeeName.

After looking at the database, it becomes apparent the HumanResources.Employee table is the one to use as it contains employee birthdates.

At first glance, it seem like it would be pretty easy to find duplicate values in SQL server. After all, we can easily sort the data.

Duplicate Birth Dates Found in our Data

Duplicate Birth Dates

But once the data is sorted, it gets harder! Since SQL is a set based language, there is no easy way, except for using cursors, to know the previous record’s values.

If we knew these, we could just compare values, and when they were the same, flag the records as duplicates.

Luckily, there is another way for us to do this. We’ll use an INNER JOIN to match employee birthdays. By doing so, we’ll get a list of employees sharing the same birthdate.

This is going to be a build-as you go article. I’ll start out with a simple query, show results, and point out what needs refinement and move on. We’ll start out with getting a list of employees and their birthdates.

Step 1 – Get a List of Employees Sorted By Birthdate

When working with SQL, especially in uncharted territory, I feel it is better to build a statement in small steps, verifying results as you go, rather than writing the “final” SQL in one step, to only find I need to troubleshoot it.

Hint: If you’re working with a very large database, then it may make sense to make a smaller copy as your dev or test version and use that to write your queries. That way, you don’t kill the production database’s performance and get everyone down on you.

So for our first step, we are going to list all employees. To do so, we’ll join the Employee table to the Person table to so we can get the employee’s name.

Here is the query so far

SQL
SELECT   E1.BusinessEntityID,
         P.FirstName + ' ' + p.LastName AS FullName,
         E1.BirthDate
FROM     HumanResources.Employee AS E1
         INNER JOIN
         Person.Person AS P
         ON P.BusinessEntityID = E1.BusinessEntityID
ORDER BY E1.BirthDate, FullName

If you look at the result, you see we have all the elements of the HR manager’s request, except that we’re displaying every employee.

Results we want to see

Employee Names and Birth Dates

In the next step, we’ll set up the results so we can start to compare birth dates to find duplicate values.

STEP 2 – Compare Birthdates to Identify Duplicates

Now that we have a list of employees, we now need a means to compare birthdates so we can identify employees with the same birthdates. In general, these are duplicate values.

To do the comparison, we’ll do a self-join on the employee table. A self-join is just a simplified version of an INNER JOIN. We start out using BirthDate as our join condition. This ensures we’re only retrieving employees with the same birth date.

SQL
SELECT   E1.BusinessEntityID,
         E2.BusinessEntityID,
         P.FirstName + ' ' + p.LastName AS FullName,
         E1.BirthDate
FROM     HumanResources.Employee AS E1
         INNER JOIN
         Person.Person AS P
         ON P.BusinessEntityID = E1.BusinessEntityID
         INNER JOIN
         HumanResources.Employee AS E2
         ON E2.BirthDate = E1.BirthDate
ORDER BY E1.BirthDate, FullName

I added E2.BusinessEntityID to the query so you can compare the primary key from both E1 and E2. You see in many cases that they are the same.

The reason we’re focusing on BusinessEntityID is that it is the primary key and the unique identifier for the table. It becomes a highly concise and convenient means to identify a row’s results and to understand its source.

We’re getting closer to obtaining our final result, but once you check out the results, you’ll see we’re picking up the same record in both the E1 and E2 match.

Check out the items circled in red. Those are the false positives we need to eliminate from our results. Those are the same rows matching to themselves.

The good news is we are really close to just identifying the duplicates.

I circled a 100% guaranteed duplicate in blue. Notice that the BusinessEntityIDs are different. This indicates the self-join is matching BirthDate on different rows – true duplicates to be sure.

Duplicate Data and False Positives Identified

Duplicates Identified, but with False Positives

In the next step, we’ll take those false positives head on and remove them from our results.

Step 3 – Eliminate Matches to Same Row – Remove False Positives

In the prior step, you may have noticed all the false positive matches have the same BusinessEntityID; whereas, the true duplicates were not equal.

This is our big hint.

If we want to only see duplicates, then we need to only bring back matches from the join where the BusinessEntityID values are not equal.

To do this, we can add:

SQL
E2.BusinessEntityID <> E1.BusinessEntityID

As a join condition to our self-join. I’ve highlighted the added condition in bold.

SQL
SELECT   E1.BusinessEntityID,
         E2.BusinessEntityID,
         P.FirstName + ' ' + p.LastName AS FullName,
         E1.BirthDate
FROM     HumanResources.Employee AS E1
         INNER JOIN
         Person.Person AS P
         ON P.BusinessEntityID = E1.BusinessEntityID
         INNER JOIN
         HumanResources.Employee AS E2
         ON E2.BirthDate = E1.BirthDate
            AND E2.BusinessEntityID <> E1.BusinessEntityID
ORDER BY E1.BirthDate, FullName

Once this query is run, you’ll see there are fewer rows in the results, and those which remain are truly duplicates.

False Positives Removed from Identified Duplicates

Duplicates with False Positives Removed

Since this was a business request, let’s clean up the query so we are only showing the information requested.

Step 4 – Final Touches

Let’s get rid of the BusinessEntityID values from the query. They were there only to help us troubleshoot.

The final query is listed here:

SQL
SELECT   P.FirstName + ' ' + p.LastName AS FullName,
         E1.BirthDate
FROM     HumanResources.Employee AS E1
         INNER JOIN
         Person.Person AS P
         ON P.BusinessEntityID = E1.BusinessEntityID
         INNER JOIN
         HumanResources.Employee AS E2
         ON E2.BirthDate = E1.BirthDate
            AND E2.BusinessEntityID <> E1.BusinessEntityID
ORDER BY E1.BirthDate, FullName

And here are the results you can present to the HR Manager!

Presentable Results

Presentable Results

Mark, one of my readers, pointed out to me that if there are three employees that have the same birth dates, then you would have duplicates in the final results. I verified this and that is true. To return a list show each duplicate only once, you can use the DISTINCT clause. This query works in all cases:

SQL
SELECT   DISTINCT P.FirstName + ' ' + p.LastName AS FullName,
         E1.BirthDate
FROM     HumanResources.Employee AS E1
         INNER JOIN
         Person.Person AS P
         ON P.BusinessEntityID = E1.BusinessEntityID
         INNER JOIN
         HumanResources.Employee AS E2
         ON E2.BirthDate = E1.BirthDate
            AND E2.BusinessEntityID <> E1.BusinessEntityID
ORDER BY E1.BirthDate, FullName

Final Comments

To summarize, here are the steps we took to identify duplicate data in our table.

  1. We first created a query of the data we want to view. In our example, this was the employee and their birth date.
  2. We performed a self-join, INNER JOIN on the same table in geek speak, and using the field we deemed a duplicated. In our case, we wanted to find duplicate birthdays.
  3. Finally, we eliminated matches to the same row by excluding rows where the primary keys were the same.

By taking a step by step approach, you can see we took a lot of the guess work out of creating the query.

If you’re looking to improve how you write your queries or are just confounded by it all and looking for a way to clear the fog, then may I suggest my guide Three Steps to Better SQL.

The post How can I find Duplicate Values in SQL Server? appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
QuestionPossible Alternative Pin
JonathanFahey6-Jan-16 11:54
JonathanFahey6-Jan-16 11:54 
QuestionGratz Pin
wouterst4-Jan-16 22:20
wouterst4-Jan-16 22:20 
AnswerRe: Gratz Pin
essentialSQL5-Jan-16 0:15
essentialSQL5-Jan-16 0:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.