Removing Duplicates with DISTINCT

The Basics: Getting a Unique List

The simplest use of DISTINCT is to get a list of unique values from a single column. Imagine you have a products table and want to see a list of all the unique supplier IDs to understand which suppliers are actively providing products. Without DISTINCT, you'd get a supplier ID for every single product, resulting in many duplicates.

The syntax is straightforward: you place the DISTINCT keyword immediately after SELECT and before the column names.

SELECT DISTINCT supplier_id
FROM products;

This query will return a list where each supplier_id appears only once, no matter how many products that supplier provides.

Let's watch a short video that demonstrates this fundamental use case.

DISTINCT Across Multiple Columns

What happens when you use DISTINCT with more than one column? This is a crucial point: DISTINCT applies to the entire row of the result set. It doesn't find unique values for each column individually. Instead, it finds unique combinations of values across all selected columns.

For example, consider this query:

SELECT DISTINCT country, city
FROM customers;

This query will return rows where the combination of country and city is unique.

• ('USA', 'New York') is one distinct row.
• ('USA', 'Boston') is another distinct row.
• A second customer from ('USA', 'New York') would be considered a duplicate and would be removed from the result set.

The following video segment explains this concept clearly.

SELECT DISTINCT service_name, error_level
FROM logs;

Under the Hood: How DISTINCT Works

Given your interest in low-level systems, you might wonder how the database actually finds and removes these duplicates. It's not magic—it's an algorithm with a computational cost. Database systems typically use one of two strategies:

1. Sorting: The database first sorts the entire result set based on the columns in your SELECT list. Once sorted, duplicate rows will be adjacent to each other, making them easy to identify and discard. The computational complexity of sorting is generally O(n log n), so this can be intensive for very large datasets.

2. Hashing: The database creates a temporary hash table in memory. It then iterates through each row of the result set, computes a hash value for the row, and checks if that hash already exists in the table.

   • If the hash is new, the row is added to the results and its hash is stored.
   • If the hash already exists, the row is a duplicate and is discarded.
     This approach is often faster (closer to O(n)) if there's enough memory to hold the hash table.

The database's query optimizer chooses which method to use based on factors like the size of the data, available memory, and whether the data is already sorted by an index. This is why DISTINCT isn't "free"—it adds a processing step to your query.

DISTINCT vs. GROUP BY

You might notice that SELECT DISTINCT column FROM table; produces the same list of unique values as SELECT column FROM table GROUP BY column;.

So when should you use which?

• Use DISTINCT when you only want a list of unique values. It clearly states your intent and is often more performant because the database knows it doesn't need to prepare for any aggregate calculations.
• Use GROUP BY when you need to perform an aggregate function (like COUNT, SUM, AVG) on groups of data.

This next video segment directly compares the two.

A Powerful Combination: COUNT(DISTINCT ...)

DISTINCT can also be used inside an aggregate function, which is an extremely common and useful pattern. This allows you to perform a calculation on only the unique values within a group.

The most frequent use is COUNT(DISTINCT column). Let's clarify the difference between the COUNT variations:

• COUNT(*): Counts all rows in the group.
• COUNT(column): Counts all rows in the group where column is not NULL.
• COUNT(DISTINCT column): Counts the number of unique non-NULL values in column for that group.

For example, SELECT agent_id, COUNT(DISTINCT customer_id) FROM service_tickets GROUP BY agent_id; would tell you how many unique customers each support agent has interacted with.

This final video clip demonstrates this powerful technique.

A Practical Warning: DISTINCT as a "Code Smell"

As you move into a developer or advanced technical support role, here is a high-ROI piece of advice. If you write a complex query with multiple JOINs and find you are getting unexpected duplicate rows, your first instinct might be to add SELECT DISTINCT to "fix" it.

Be careful. While this might clean up your output, it often just masks a deeper problem in your query logic. The duplicate rows are usually a symptom of a flawed JOIN condition (e.g., you joined on user_id but also needed to join on order_id).

Instead of using DISTINCT as a patch, treat the duplicates as a signal to investigate why they are appearing. Fixing the underlying JOIN is a much more robust and efficient solution than asking the database to fetch extra, incorrect data and then filter it out.

Conclusion

Today you've added a versatile tool to your SQL toolkit. While simple on the surface, DISTINCT has important nuances in how it operates and where it's best applied.

Key Takeaways:

• SELECT DISTINCT removes entire duplicate rows from a result set.
• When used on multiple columns, it finds unique combinations of values.
• It is not a "free" operation; the database uses sorting or hashing algorithms, which have a performance cost.
• COUNT(DISTINCT column) is a powerful pattern for counting unique occurrences within a group.
• Using DISTINCT to hide duplicates from a bad JOIN is often a "code smell" that indicates a deeper problem with your query logic.

Preview of the Next Lesson:
We've now seen how to filter individual rows with WHERE and how to create groups with GROUP BY. But what if you want to filter the groups themselves? For example, "show me only the agents who have handled more than 50 tickets." The WHERE clause can't do this because it operates before grouping. In the next lesson, we will introduce the HAVING clause, which is designed specifically for this purpose.