Back to articles list Articles Cookbook
9 minutes read

How Do You Get Rid of Duplicates in an SQL JOIN?

Do you have unwanted duplicates from your SQL JOIN query? In this article, I’ll discuss the possible reasons for getting duplicates after joining tables in SQL and show how to fix a query depending on the reason behind the duplicates.

Data analysts with little experience in SQL JOINs often encounter unwanted duplicates in the result set. It’s challenging for beginners to identify the reason behind these duplicates in JOINs.

In this article, I’ll discuss the most common issues leading to duplicates in SQL JOIN outputs. I’ll also show possible solutions to these common issues.

If you want to get a comprehensive understanding of SQL JOINs, check out this interactive course that provides coding challenges for all JOIN types.

Let’s start with a very brief overview of SQL JOINs.

SQL JOINs Overview

JOIN is an SQL construct for requesting information from two or more tables within the same query.

For example, let’s say you have a list of the top 100 movies from the 20th century, and you want to subset it to the movies made by currently living directors. In your movies table, you don’t have detailed information on the movie directors, just their IDs. But you do have a separate directors table, with the ID, the full name, the birth year, and the death year (if applicable) of each director.

In your query, you can join two tables by the director’s ID to get a list of movies made by currently living directors:

SELECT movies.title, directors.full_name
FROM movies
JOIN directors
ON movies.director_id = directors.id
WHERE directors.death_year IS NULL;

As you can see, we specify the tables we want to join in the FROM and JOIN clauses. Then in the ON clause, we specify the columns from each table to be used for joining these tables. If you are new to SQL JOINs, check out this introductory guide. Here’s also an SQL JOIN cheat sheet with syntax and examples of different JOINs.

The SQL JOIN is a great tool that provides a variety of options beyond the simple join of two tables. If you are not familiar with SQL JOIN types, read this article that explains them with illustrations and examples. Depending on your use case, you can choose INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. You may even need to join tables without a common column or join more than two tables.

Now, let’s see how these different JOINs may result in unwanted duplicates.

What Are the Reasons for Duplicates in SQL JOINs?

There are many possible reasons for getting duplicates in the result of your SQL JOIN query. I’ll go through the top 5 reasons; for each one, I’ll show a sample query with the problem and a corrected query to get a result without duplicates.

Let’s start by briefly reviewing the data to be used for our examples. Imagine we run a real estate agency that sells houses somewhere in the United States. We have tables with agents, customers, and sales. See below for what data is stored in each table.

agents
idfirst_namelast_nameexperience_years
1KateWhite5
2MelissaBrown2
3AlexandrMcGregor3
4SophiaScott3
5StevenBlack1
6MariaScott1

customers
idfirst_namelast_nameemail
11XavieraLopezxaviera111111@gmail.com
12GabrielCumberlygabriel111111@gmail.com
13ElisabethStevenselisabeth111111@gmail.com
14OprahWinfreyoprah111111@gmail.com
15IvanLeeivan111111@gmail.com

sales
idhouse_iddateagent_first_nameagent_last_namecustomer_idprice
10110122021-11-03KateWhite141200000
10221342021-12-06SophiaScott12950000
10310152021-12-10MariaScott13800000
10420132021-12-12AlexandrMcGregor151350000
10521122021-12-12AlexandrMcGregor151450000
10610102022-01-10StevenBlack111500000

Without any further delay, let’s move to our examples.

1. Missing ON Condition

Beginners unfamiliar with SQL JOINs often simply list the tables in FROM without specifying the JOIN condition at all when trying to combine information from two or more tables. This is valid syntax, so you do not get any error messages. But the result is a cross join with all rows from one table combined with all rows from another table.

For example, suppose we want to get information on the customer who bought a particular house (ID #2134). If we use the following query:

SELECT house_id, first_name, last_name, email
FROM sales, customers
WHERE house_id = 2134;

This is the result we get:

house_idfirst_namelast_nameemail
2134XavieraLopezxaviera111111@gmail.com
2134GabrielCumberlygabriel111111@gmail.com
2134ElisabethStevenselisabeth111111@gmail.com
2134OprahWinfreyoprah111111@gmail.com
2134IvanLeeivan111111@gmail.com

Instead of one record with the customer we want, we have all our customers listed in the result set.

To fix the query, you need an explicit JOIN syntax. The tables to be combined are specified in FROM and JOIN, and the join condition is specified in the ON clause:

SELECT s.house_id, c.first_name, c.last_name, c.email
FROM sales s
JOIN customers c
ON s.customer_id = c.id
WHERE s.house_id = 2134;

Here, we specify the customer ID from the sales table to match the customer ID from the customers table. This gives us the desired result:

house_idfirst_namelast_nameemail
2134GabrielCumberlygabriel111111@gmail.com

You could specify the join condition in the WHERE clause to get the same result. But that is against the intended use of the WHERE clause. Also, there are additional benefits from using the JOIN syntax rather than listing the tables in FROM. Check out this article to understand why the JOIN syntax is preferred.

2. Using an Incomplete ON Condition

Unwanted rows in the result set may come from incomplete ON conditions. In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.

Let’s say we want to see the experience level of the real estate agent for every house sold. If we start by joining the sales and agents tables by the agent’s last name:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
ORDER BY s.house_id;

this is what you get:

house_idfirst_namelast_nameexperience_years
1010StevenBlack1
1012KateWhite5
1015MariaScott1
1015SophiaScott3
2013AlexandrMcGregor3
2112AlexandrMcGregor3
2134MariaScott1
2134SophiaScott3

That didn’t work well. We have two different agents with the last name Scott: Maria and Sophia. As a result, houses #1015 and #2134 are each included twice with different agents.

To fix this query, we need to join the sales and agents tables using two pairs of columns, corresponding to the last name and the first name of the agent:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name
ORDER BY s.house_id;

And here is the result we were looking for.

house_idfirst_namelast_nameexperience_years
1010StevenBlack1
1012KateWhite5
1015MariaScott1
2013AlexandrMcGregor3
2112AlexandrMcGregor3
2134SophiaScott3

While JOIN is one of the basic tools in SQL, you need to be aware of the many different nuances to join tables effectively. I recommend practicing SQL JOINs with this interactive course that covers a variety of joining scenarios with 93 coding challenges.

3. Selecting a Subset of Columns

In some cases, the records in the result set are not duplicates but appear as if they are because the selected subset of columns doesn’t show all differences between records.

For example, imagine we want to see the dates each real estate agent sold a house. If we use the following query:

SELECT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

it produces the following result:

first_namelast_namedate
KateWhite2021-11-03
SophiaScott2021-12-06
AlexandrMcGregor2021-12-12
AlexandrMcGregor2021-12-12
MariaScott2021-12-10
StevenBlack2022-01-10

The result set includes two records with Alexandr McGregor that appear identical. However, if you add house ID to the SELECT statement, you see these two records correspond to the sale of two different houses on the same day.

If you are not interested in this additional information and want to have only one row displayed here, use DISTINCT:

SELECT DISTINCT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

Now, the result is:

first_namelast_namedate
KateWhite2021-11-03
SophiaScott2021-12-06
AlexandrMcGregor2021-12-12
MariaScott2021-12-10
StevenBlack2022-01-10

4. Listing Matching Rows Only

A similar problem may occur if you want to list only the rows from one table but there are several matching records in the other table. You end up with unwanted duplicates in your result set.

For instance, say we want to list all customers who bought houses via our agency. If we use the following query:

SELECT c.first_name, c.last_name, c.email
FROM customers c
JOIN sales s
ON c.id = s.customer_id;

here is the result:

first_namelast_nameemail
GabrielCumberlygabriel111111@gmail.com
ElisabethStevenselisabeth111111@gmail.com
XavieraLopezxaviera111111@gmail.com
OprahWinfreyoprah111111@gmail.com
IvanLeeivan111111@gmail.com
IvanLeeivan111111@gmail.com

As you see, the resulting table includes Ivan Lee twice. This is because he bought two houses and there are two corresponding records in the sales table. One possible solution is to use DISTINCT as in the previous example. An even better solution is to avoid using SQL JOIN at all by filtering the result set using the EXISTS keyword:

SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE EXISTS (SELECT customer_id FROM sales);

Now, the result is:

first_namelast_nameemail
GabrielCumberlygabriel111111@gmail.com
ElisabethStevenselisabeth111111@gmail.com
XavieraLopezxaviera111111@gmail.com
OprahWinfreyoprah111111@gmail.com
IvanLeeivan111111@gmail.com

This gives you the desired output and also makes the intention of your query clearer.

5. Using Self Joins

Finally, unwanted duplicates in JOINs often result from an incorrect specification of joining conditions in self joins – that is, when a table is joined with itself.

Let’s say we want our agents to form pairs for our next training. Obviously, we don’t want any agent to be paired with himself/herself. So, we might specify the ON condition a1.id <> a2.id:

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
ORDER BY a1.id;

However, this query outputs each pair twice. For example, in the first row of the table below, Kate White is considered Agent 1, and Maria Scott is considered Agent 2. But closer to the end of the table, you get the same pair of agents but with Maria Scott as Agent 1 and Kate White as Agent 2.

agent1_first_nameagent1_last_nameagent1_experienceagent2_first_nameagent2_last_nameagent2_experience
KateWhite5MariaScott1
KateWhite5StevenBlack1
KateWhite5MelissaBrown2
KateWhite5SophiaScott3
KateWhite5AlexandrMcGregor3
MelissaBrown2StevenBlack1
MelissaBrown2SophiaScott3
MelissaBrown2MariaScott1
MelissaBrown2AlexandrMcGregor3
MelissaBrown2KateWhite5
AlexandrMcGregor3MariaScott1
AlexandrMcGregor3MelissaBrown2
AlexandrMcGregor3SophiaScott3
AlexandrMcGregor3KateWhite5
AlexandrMcGregor3StevenBlack1
SophiaScott3MariaScott1
SophiaScott3StevenBlack1
SophiaScott3AlexandrMcGregor3
SophiaScott3MelissaBrown2
SophiaScott3KateWhite5
StevenBlack1SophiaScott3
StevenBlack1AlexandrMcGregor3
StevenBlack1MariaScott1
StevenBlack1MelissaBrown2
StevenBlack1KateWhite5
MariaScott1KateWhite5
MariaScott1AlexandrMcGregor3
MariaScott1SophiaScott3
MariaScott1StevenBlack1
MariaScott1MelissaBrown2

To solve this issue, you need to add an explicit condition to include each pair only once. One common solution is to specify the joining condition a1.id < a2.id. With this, you get the pair Kate White and Maria Scott but not vice versa. This is because Kate’s ID (1) is a lower number than Maria’s ID (6).

In practice, you may have some other conditions for pairing the agents. For instance, you may want to pair more experienced agents (3+ years) with less experienced ones (< 3 years). The corresponding filtering condition in WHERE solves the problem:

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
WHERE a1.experience_years>=3 AND a2.experience_years < 3
ORDER BY a1.id;

Here’s the result:

agent1_first_nameagent1_last_nameagent1_experienceagent2_first_nameagent2_last_nameagent2_experience
KateWhite5StevenBlack1
KateWhite5MelissaBrown2
KateWhite5MariaScott1
AlexandrMcGregor3MariaScott1
AlexandrMcGregor3StevenBlack1
AlexandrMcGregor3MelissaBrown2
SophiaScott3MariaScott1
SophiaScott3StevenBlack1
SophiaScott3MelissaBrown2

This result set looks much better and makes it easier to select three pairs, each consisting of an agent with more experience and another with less experience.

Let’s Practice SQL JOINs!

Joining tables in SQL is not that difficult. But it does require a lot of practice. If you want to avoid pitfalls like unwanted duplicates in JOINs and missing records, follow this guide on practicing SQL JOINs.

If you have only basic experience with SQL and want to combine data more confidently from multiple tables, I recommend this SQL JOINs interactive course. It covers all major types of JOINs as well as joining a table with itself, joining multiple tables in one query, and joining tables on non-key columns. Get more details about this course in this overview article.

Bonus. Here are the top 10 SQL JOIN interview questions with answers.

Thanks for reading, and happy learning!