LEFT JOIN Without Duplicates

How to Avoid Duplicates in SQL Left Joins

Have you ever experienced running a left join in SQL only to find more rows in the result set than expected? This is a common issue, and in this blog post, we’ll explain why it happens and how to fix it using a simple example.

The Problem: Duplicates in a Left Join

Imagine you have two tables: an Employee table and a Department table. Your goal is to join them and find out which department each employee works in. The Employee table contains just employee names and their department IDs, while the Department table has additional details, like department name, the manager, and valid dates for the manager.

When you perform a left join between the two tables on the Department ID, you might expect one row for each employee. However, sometimes, you end up with duplicate rows. Why?

Let’s say Bob works in IT, but after the join, you see two rows for Bob. One row shows John as his manager, and another shows Ben as his manager. The issue arises because there are multiple records for IT in the Department table, with different managers over time.

Fixing the Issue

1. Add a Time Range to the Join: If the Department table includes a validity range (valid-from and valid-to dates), you can refine the join condition. Instead of joining solely on Department ID, you can add a condition to ensure you’re joining based on the current department manager:

    

    

   ON employee.department_id = department.department_id AND GETDATE() BETWEEN department.valid_from AND department.valid_to

   This will give you only the current department manager and eliminate duplicates for employees like Bob.

2. Use DISTINCT: If the validity of the manager isn't important, but duplicates still exist, you can use DISTINCT to filter out the extra rows:

    

   SELECT DISTINCT employee.name, department.department_name FROM employee LEFT JOIN department ON employee.department_id = department.department_id;

3. String Aggregation: Another option is to aggregate the differing values into a single column. For example, you can combine multiple managers into a comma-separated list:

   SELECT employee.name, STRING_AGG(department.manager, ', ') AS managers FROM employee LEFT JOIN department ON employee.department_id = department.department_id GROUP BY employee.name;

   This method keeps all the data while avoiding duplicates.

Final Thoughts

Handling duplicates in left joins can be tricky, but with the right approach—whether it's adding extra criteria, using DISTINCT, or aggregating values—you can keep your result set clean and accurate. Try these techniques next time you run into duplicates in your SQL queries!

Having trouble to remember SQL syntax? Check out my free SQL Cheat Sheet!