SELECT DISTINCT in SQL: Removing Duplicate Records from Query Results

Real-world tables are full of repetition — many orders share the same customer, many products share the same category. Sometimes you don't want every row; you just want to know the unique values present. SELECT DISTINCT solves exactly that problem, collapsing duplicate rows in a result set down to one representative row each.

What Is SELECT DISTINCT?

SELECT DISTINCT is a modifier on the SELECT statement that removes duplicate rows from the result set, returning only unique combinations of the selected columns. When used with a single column, it returns each unique value once; when used with multiple columns, it returns each unique combination of those columns once.

What You'll Learn

• Use SELECT DISTINCT to retrieve unique values from a single column.
• Understand DISTINCT behavior across multiple selected columns.
• Recognize the performance cost of DISTINCT on large tables.
• Distinguish DISTINCT from GROUP BY at a conceptual level.

Key Terms to Know

• DISTINCT: A SELECT modifier that removes duplicate rows from the result set.
• Unique combination: When DISTINCT spans multiple columns, the full set of values across those columns together must be unique, not each column individually.
• Duplicate row: A row that has identical values in all selected columns as another row in the same result set.

DISTINCT on a Single Column

SELECT DISTINCT category FROM products; returns each unique category value exactly once, no matter how many products share that category. This is a fast, common way to discover what distinct values actually exist in a column — useful for populating dropdown filters in an application, for example.

DISTINCT Across Multiple Columns

SELECT DISTINCT category, brand FROM products; returns each unique (category, brand) combination once. Two rows are only considered duplicates of each other if every selected column matches — a row with ('electronics', 'Sony') is different from ('electronics', 'Samsung'), even though they share the same category.

This distinction matters: people sometimes expect DISTINCT to apply separately to each column, but it actually applies to the entire row formed by all selected columns together.

Performance Considerations and DISTINCT vs GROUP BY

DISTINCT requires MySQL to compare every row against every other row (often using sorting or hashing internally) to identify duplicates, which can become expensive on very large tables without supporting indexes. It's worth using only when genuinely needed, not as a reflexive habit to 'be safe.'

DISTINCT and GROUP BY can sometimes achieve a similar result — GROUP BY category is comparable to SELECT DISTINCT category — but GROUP BY is specifically designed for combining with aggregate functions like COUNT or AVG per group, while DISTINCT is purely about eliminating duplicate rows from the output.

Visual Summary

Picture a long line of customer survey forms, several of which were accidentally filled out and submitted twice with completely identical answers. SELECT DISTINCT is the person at the front desk who sorts through the stack and keeps only one copy of each truly identical form, discarding the exact repeats while keeping forms that differ in even one answer.

DISTINCT Behavior Examples

Query	What It Returns
SELECT DISTINCT category FROM products;	Each unique category value, once
SELECT DISTINCT category, brand FROM products;	Each unique (category, brand) pair, once
SELECT DISTINCT * FROM products;	Only fully identical rows across all columns are merged

SQL Example

-- Unique categories available in the products table
SELECT DISTINCT category FROM products;

-- Unique (category, brand) combinations
SELECT DISTINCT category, brand FROM products;

-- Counting how many distinct categories exist
SELECT COUNT(DISTINCT category) AS total_categories FROM products;

The first query is ideal for populating a category filter dropdown in an application without duplicate entries. The second shows that DISTINCT considers the full combination of both columns, so the same category can legitimately appear multiple times paired with different brands. The third combines DISTINCT with COUNT to answer a slightly different question: not what the unique values are, but how many of them exist.

Real-World Examples

• E-commerce filter sidebars use SELECT DISTINCT category, brand, color to populate filter options without showing duplicate entries.
• Analytics dashboards use COUNT(DISTINCT user_id) to measure unique active users rather than total event rows.
• Marketing teams use SELECT DISTINCT country FROM customers; to quickly see which countries currently have any customers at all.
• Data cleaning scripts use DISTINCT-based queries to identify how much true duplication exists in an imported dataset before deciding on a cleanup strategy.

Best Practices and Pro Tips

• Use DISTINCT deliberately, not defensively — adding it 'just in case' on every query adds unnecessary processing overhead when duplicates were never actually possible in the first place.
• When you need a duplicate count rather than just unique values, reach for COUNT(DISTINCT column) instead of combining DISTINCT and COUNT incorrectly.
• For populating UI filter options from large tables, consider whether an index on the relevant column could speed up the DISTINCT operation, especially if it's run frequently.

Common Mistakes to Avoid

• Assuming SELECT DISTINCT col1, col2 deduplicates col1 and col2 independently of each other — it actually deduplicates the row formed by both together.
• Reaching for DISTINCT when the real problem is actually a need for GROUP BY with aggregation, leading to a query that doesn't actually answer the intended question.
• Overusing DISTINCT on very large, unindexed tables without realizing the performance cost of the underlying deduplication process.
• Forgetting that DISTINCT applies to the entire SELECT list, including any computed or aliased columns, which can unexpectedly reduce deduplication if a column varies slightly between otherwise similar rows.

Interview Questions

Q1. What does SELECT DISTINCT do when applied to multiple columns?

It returns each unique combination of values across all the selected columns together, not each column deduplicated separately. Two rows are duplicates only if every selected column matches between them.

Q2. What's the difference between SELECT DISTINCT category and SELECT category GROUP BY category?

Both return the same set of unique category values in simple cases. GROUP BY is specifically designed to support aggregate functions like COUNT or AVG per group, while DISTINCT exists purely to remove duplicate rows from a result set.

Q3. How would you count the number of unique customers who placed an order?

Using COUNT(DISTINCT customer_id) FROM orders;, which counts how many distinct customer_id values appear, rather than counting every order row, which could count the same customer multiple times.

Q4. Why can SELECT DISTINCT be expensive on large tables?

MySQL must compare rows against each other (typically via sorting or hashing internally) to identify and remove duplicates, which adds processing overhead that grows with the size of the table being queried.

Practice MCQs

1. What does SELECT DISTINCT category, brand FROM products; return?

1. Unique category values only
2. Unique brand values only
3. Each unique combination of category and brand together
4. All rows with no deduplication

Answer: C. Each unique combination of category and brand together

Explanation: DISTINCT across multiple columns deduplicates based on the full combination of those columns, not each column independently.

2. Which function correctly counts unique customer IDs in an orders table?

1. COUNT(customer_id)
2. COUNT(DISTINCT customer_id)
3. DISTINCT(customer_id)
4. SUM(DISTINCT customer_id)

Answer: B. COUNT(DISTINCT customer_id)

Explanation: COUNT(DISTINCT column) counts only unique values in that column, correctly avoiding counting the same customer multiple times.

3. Why might overusing SELECT DISTINCT hurt performance?

1. It locks the table permanently
2. It requires comparing rows to identify and remove duplicates, adding overhead
3. It always creates a new table
4. It disables indexes

Answer: B. It requires comparing rows to identify and remove duplicates, adding overhead

Explanation: The deduplication process behind DISTINCT requires extra computation, which can become costly on very large, unindexed tables.

Quick Revision Points

• SELECT DISTINCT removes duplicate rows from the result set based on all selected columns combined.
• Multi-column DISTINCT deduplicates the full row, not each column separately.
• COUNT(DISTINCT column) counts unique values, different from a plain COUNT(column).
• DISTINCT can be costly on large unindexed tables; use deliberately, not defensively.

Conclusion

• DISTINCT is the right tool specifically for eliminating duplicate rows, not a general-purpose aggregation tool.
• Multi-column DISTINCT behavior is a common source of confusion worth remembering clearly.
• COUNT(DISTINCT column) is the correct pattern for counting unique values rather than total rows.

SELECT DISTINCT removes duplicate rows from a query's result set, working on the full combination of all selected columns rather than each column independently. It's invaluable for discovering unique values, populating filter UI, or measuring distinct counts with COUNT(DISTINCT ...), but should be used deliberately given its performance cost on large tables. With duplicate handling covered, the next lesson moves to the WHERE clause — the primary tool for filtering which rows a query returns in the first place.