Distinct Values and Set Filtering
In this lesson, you will learn how to clean up your query results by removing duplicate rows so you can focus on the unique values within your dataset.
The Problem with Duplicates
When you perform a standard SELECT query, SQL returns every row that matches your criteria. If your table contains repeated information—such as a list of customers who have all made purchases in the same city—your output might look cluttered with the same value appearing over and over. While this is helpful if you want to see every transaction, it makes it difficult to answer questions like, "Which unique cities are represented in our customer base?"
Using the DISTINCT Keyword
To solve this, we use the DISTINCT keyword. By placing DISTINCT immediately after SELECT, you instruct the database to filter the result set so that only unique combinations of data remain. If the database finds multiple rows that are identical, it collapses them into a single representative row.
Imagine you have a Sales table with a column called Country. If you run this:
SELECT Country
FROM Sales;
You might get a list like: USA, Canada, USA, USA, Mexico.
By adding DISTINCT, you transform that list into a clean, unique set:
SELECT DISTINCT Country
FROM Sales;
The output becomes: USA, Canada, Mexico.
Why This Matters
This process is a form of Set Filtering. In relational theory, a "set" does not contain duplicates. By applying DISTINCT, you are aligning your query output with the mathematical definition of a set, which is essential for accurate reporting and data analysis. Whether you are creating a dropdown menu for a website or generating a summary report, removing redundancy ensures that your data is concise and meaningful.
Keep in mind that DISTINCT looks at the entire row of data you have projected. If you select multiple columns, the database will only remove a row if every value in that row matches another row exactly.
Key Takeaway: Use the DISTINCT keyword to filter out duplicate rows and return only unique values, transforming a raw list into a clean, distinct dataset.