DISTINCT

Precision and Uniqueness: Mastering DISTINCT

This guide provides a professional dissection of the DISTINCT keyword. We will move beyond its basic function to cover multi-column behavior, its critical relationship with aggregate functions, and its often-overlooked performance costs.

Core Concept: Filtering Duplicate Rows

The DISTINCT keyword is an operator that is applied to a SELECT statement. Its purpose is to filter out duplicate rows from the final result set.

• The Crucial Misconception: DISTINCT does not operate on a single column in isolation. It operates on the entire combination of columns listed in the SELECT clause. A row is only considered a “duplicate” if every single column value in that row is identical to the corresponding column value in another row.

Interview Scenario 1: The Standard Use Case

Interviewer: “Write a query to find all the unique CategoryIDs for which we have products in stock.”

Your Expert Answer:

“Certainly. The most direct and readable way to achieve this is with SELECT DISTINCT.”

-- Find every unique CategoryID present in the Products table.
SELECT DISTINCT
    CategoryID
FROM
    Products;

• Explanation: This query scans the Products table. For every CategoryID it encounters, it adds it to a temporary set. If it encounters a CategoryID it has already seen, it discards it. The final result is a clean list of unique category IDs.

Interview Scenario 2: The Multi-Column “Gotcha”

This is the most common trap and a key test of a candidate’s precise understanding.

Interviewer: “A junior developer wrote the following query to get a list of unique categories and the products in them. But they are confused because they are still seeing duplicate CategoryIDs in the output, like ‘Electronics’ appearing twice. Explain to them what’s wrong with their understanding of DISTINCT.”

-- The query in question
SELECT DISTINCT
    c.CategoryName,
    p.ProductName
FROM
    Products AS p
JOIN
    Categories AS c ON p.CategoryID = c.CategoryID;

Your Expert Answer:

“This is a classic and excellent learning opportunity. The developer’s mental model is that DISTINCT applies only to the first column, c.CategoryName. However, this is not how SQL works.

DISTINCT evaluates the uniqueness of the entire row defined by the SELECT list. In this case, a row is the combination of (CategoryName, ProductName).

Let’s look at the data:

• The row ('Electronics', 'Laptop') is a unique combination.
• The row ('Electronics', 'Wireless Mouse') is a different and also unique combination.

Therefore, the query is behaving correctly. It is returning the distinct pairs of category and product. Since both ‘Laptop’ and ‘Wireless Mouse’ are in the ‘Electronics’ category, the category name ‘Electronics’ correctly appears in the output for each unique product associated with it.

If the goal was simply to get a unique list of category names that have products, the ProductName should be removed from the SELECT list.”

Interview Scenario 3: DISTINCT with Aggregate Functions (COUNT)

This is a vital topic for business intelligence and reporting queries.

Interviewer: “In our Orders table, what is the business meaning of COUNT(CustomerID) versus COUNT(DISTINCT CustomerID)? Write both queries.”

Your Expert Answer:

“Both queries provide valuable but different metrics about customer activity.

1. COUNT(CustomerID): Counts the Total Number of Orders

This query counts every non-null CustomerID entry in the Orders table. Since every order must have a customer, this effectively counts the total number of orders placed.

-- Answers: "How many total orders have been placed?"
SELECT COUNT(CustomerID) AS TotalOrders FROM Orders;

If a customer places 5 orders, they will be counted 5 times.

2. COUNT(DISTINCT CustomerID): Counts the Number of Unique Customers

This query first creates an internal, temporary list of all the unique customer IDs from the Orders table and then counts the items in that list.

-- Answers: "How many different customers have ever placed an order?"
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomersWithOrders FROM Orders;

If a customer places 5 orders, they will only be counted once. This query tells you the size of your active customer base. Understanding this distinction is critical for accurate business reporting.”

The Ultimate Interview Topic: The Performance Cost of DISTINCT

Interviewer: “Your query SELECT DISTINCT CustomerID, ProductID FROM Order_Items is running very slowly on our large dataset. Why can DISTINCT be a performance bottleneck, and what is the database doing behind the scenes?”

Your Expert Answer:

“The DISTINCT keyword can be expensive because, to identify and eliminate duplicates, the database must first group all identical rows together.

The most common strategy the database uses to achieve this is to SORT the entire result set. It will sort the data based on all columns in the SELECT DISTINCT list. Once the data is sorted, finding and removing duplicates is a simple matter of comparing each row to the one before it.

This sort operation is the performance bottleneck.

• If the result set is small, the sort can happen quickly in memory.
• If the result set is large, the database may have to write the data to temporary tables on disk to perform the sort (an operation often called a ‘filesort’). This disk I/O is extremely slow.

When I see a slow DISTINCT query, my first step is to run an EXPLAIN on it. If I see ‘Using temporary’ or ‘Using filesort’ in the output, it confirms that a costly sort operation is the cause.

A potential solution could be to create a composite index that covers all the columns in the SELECT DISTINCT list (e.g., an index on (CustomerID, ProductID)). With such an index, the database can sometimes read the data in an already-sorted order, or perform a more efficient ‘unique scan’ on the index itself, completely avoiding the expensive manual sort.”

Alternative to DISTINCT: GROUP BY

Interviewer Follow-up: “Is there another way to get a unique list of CategoryIDs without using DISTINCT?”

Your Expert Answer:

“Yes. The GROUP BY clause can achieve the same result. The query would be:

SELECT CategoryID FROM Products GROUP BY CategoryID;

• How it Works: GROUP BY CategoryID collapses all rows with the same CategoryID into a single representative row. For the purpose of getting a simple unique list, the output is identical to SELECT DISTINCT CategoryID.

• Key Difference: GROUP BY is more powerful because it’s designed for aggregation. You can attach aggregate functions like COUNT(), SUM(), AVG() to get metrics for each group. DISTINCT is purely a filtering keyword. For just getting a unique list, DISTINCT is often more readable and clearly states the query’s intent.”