SQL DISTINCT: How to Deduplicate Query Results

Module 3

Deduplicating Results with DISTINCT

A step-by-step, hands-on SQL practice lesson

SELECT DISTINCT country FROM customers;

In Module 1 you learned to select columns, and in Module 2 you learned to rename them with aliases. But what happens when the same value shows up in a column over and over — ten customers all listed as "Cambodia," the same product ordered fifty times? In this lesson, you'll learn how DISTINCT removes those repeated rows so your results only show each unique value once.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Related Lessons
8. FAQ

Introduction

What You'll Learn — and Why It Matters

🎯 Objective

By the end of this lesson, you will be able to:

• Use DISTINCT to remove duplicate rows from a result set.
• Understand how DISTINCT behaves differently with one column versus several columns.
• Combine DISTINCT with ORDER BY to sort unique results.
• Use COUNT(DISTINCT column) to count how many unique values exist.
• Recognize when DISTINCT is the right tool — and when it's hiding a deeper query problem.

⚠️ What to Avoid

Common mistakes

• Assuming DISTINCT only checks the first column when you list several — it actually checks the whole row combination.
• Reaching for DISTINCT as a quick fix when a JOIN is producing extra duplicate rows, instead of fixing the join logic itself.
• Forgetting that DISTINCT treats every NULL value as equal to every other NULL — they collapse into a single row.

💡 Important to Know

Before you start

DISTINCT applies to the entire selected row, not to one column in isolation. SELECT DISTINCT city, country keeps a row only if that exact combination of city and country hasn't already appeared — two rows with the same city but different countries are still both kept.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, or Linux — this lesson works identically on all of them.

Database ServerMySQL installed and running. See How to Install MySQL if you haven't set it up yet.

Optional but recommendedXAMPP with phpMyAdmin, for a visual way to run queries. See How to Install XAMPP.

Prior knowledgeComfortable with SELECT column FROM table; from Module 1 and Module 2.

Practice

Step-by-Step Practice

Follow along in order — each step builds on the one before it.

1 Open MySQL (CLI or phpMyAdmin)

Open a terminal and connect with the MySQL command-line client, or open phpMyAdmin in your browser at http://localhost/phpmyadmin/.

mysql -u root -p

The basic syntax pattern for this lesson looks like this:

SELECT DISTINCT column1, column2 FROM table_name;

2 Create a Database and Verify It

Create a fresh practice database and confirm it exists before moving on.

CREATE DATABASE module3_practice;
SHOW DATABASES;
USE module3_practice;

Now create a table and verify it was created:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  country VARCHAR(50),
  product VARCHAR(50)
);
SHOW TABLES;
DESCRIBE orders;

3 Insert Sample Data (with Deliberate Duplicates)

Add rows where the country and product columns repeat on purpose, so you have real duplicates to work with.

INSERT INTO orders (id, customer_name, country, product) VALUES
(1, 'Sokha', 'Cambodia', 'Keyboard'),
(2, 'Maria', 'Spain', 'Mouse'),
(3, 'Wei', 'China', 'Keyboard'),
(4, 'Dara', 'Cambodia', 'Monitor'),
(5, 'Liam', 'Ireland', 'Mouse'),
(6, 'Nary', 'Cambodia', 'Keyboard');

4 See the Duplicates First (Baseline)

Run a plain SELECT without DISTINCT so you can see "Cambodia" and "Keyboard" repeating.

SELECT country FROM orders;

5 Remove Duplicates from a Single Column

Add DISTINCT right after SELECT to collapse the repeats.

SELECT DISTINCT country FROM orders;

6 DISTINCT Across Multiple Columns

Now watch how the behavior changes with two columns — rows are only removed if both values match an earlier row.

SELECT DISTINCT country, product FROM orders;

Notice

"Cambodia, Keyboard" appears twice in the raw data (Sokha and Nary) and collapses to one row. But "Cambodia, Monitor" is kept separately, because the combination is different — even though "Cambodia" alone repeats three times.

7 DISTINCT with ORDER BY

Sort your unique results alphabetically, just like any other query.

SELECT DISTINCT country FROM orders ORDER BY country;

8 Count the Unique Values

Use COUNT(DISTINCT column) to find out how many unique countries placed orders — without listing them all out.

SELECT COUNT(DISTINCT country) AS unique_countries FROM orders;

9 Compare DISTINCT vs. GROUP BY

For simple deduplication, GROUP BY produces the same result as DISTINCT — it's worth seeing both side by side, since you'll meet GROUP BY again in a later module.

SELECT country FROM orders GROUP BY country;

Troubleshooting

Common Problems and Solutions

Problem 1: DISTINCT doesn't seem to remove any duplicates

You added DISTINCT, but every row still shows up, even ones that look identical.

SolutionCheck whether you're also selecting a column like id that's unique on every row. Since DISTINCT compares the whole row, one unique column is enough to make every row "different." Remove it from the SELECT list if you don't actually need it in the output.

Problem 2: DISTINCT is very slow on a large table

The query works, but takes a long time to return results once the table grows to millions of rows.

SolutionDISTINCT often requires sorting or hashing the full result set to find duplicates, which gets expensive at scale. Add an index on the column(s) you're deduplicating, or consider whether GROUP BY with an index performs better for your specific case.

Problem 3: Confusing NULL handling

You expected multiple NULL values in a column to each show up, but DISTINCT only returns one row with NULL.

SolutionThis is expected behavior — for the purposes of DISTINCT, MySQL treats all NULL values as equal to each other, so they collapse into a single row, unlike normal comparisons where NULL never equals anything.

Problem 4: Using DISTINCT to "fix" a JOIN that returns too many rows

You added DISTINCT after a JOIN started producing duplicate rows, and it made the symptom disappear.

SolutionDISTINCT can mask a join that's matching more rows than it should, without fixing the actual cause. Double-check your join conditions first — the duplicates are usually a sign the relationship between tables needs a closer look, not just a cleanup step at the end.

Conclusion

What You Should Take Away

By practicing these nine steps, you should now be comfortable using DISTINCT to clean up repeated values, understand how it evaluates full rows rather than single columns, and know when COUNT(DISTINCT ...) is more useful than listing every unique value out. Just as importantly, you should recognize when DISTINCT is genuinely the right tool — and when it's covering up a deeper issue in your query.

In Module 4, we'll build on this by learning how to filter rows precisely with the WHERE clause, combining everything you've learned so far into complete, real-world queries.

Homework

Homework & Quiz

📝 Homework Task

Using the orders table from this lesson, write your own query that:

• Returns each unique product that has been ordered.
• Sorts the results alphabetically.
• Then write a second query that counts how many unique products there are, using COUNT(DISTINCT ...).

Try writing both yourself before checking — there's no single "correct" answer as long as the logic matches these requirements.

🧪 Quiz: Test Your Understanding (10 Questions)

Answer all 10, then click Submit to see your score instantly.

1. Which keyword removes duplicate rows from a result set?

UNIQUE DISTINCT REMOVEDUP NODUP

2. What does SELECT DISTINCT country, product FROM orders; actually compare?

Only the country column Only the product column The combination of both columns together Neither column, it compares the whole table

3. If a table has rows (Cambodia, Keyboard) twice and (Cambodia, Monitor) once, how many rows does SELECT DISTINCT country, product return?

2 1 3 0

4. What does COUNT(DISTINCT country) return?

The total number of rows in the table The number of unique countries A list of every country An error, since COUNT can't be combined with DISTINCT

5. How does DISTINCT treat multiple NULL values in the same column?

It treats them all as equal and keeps only one It keeps every NULL as a separate row It throws an error It converts NULL to an empty string

6. Why might including a table's id column defeat the purpose of DISTINCT?

id columns are always NULL Since id is unique per row, every row becomes "different" again id columns can't be selected at all It doesn't — id has no effect on DISTINCT

7. Which clause can you safely combine with DISTINCT to sort your unique results?

GROUP UNIQUE BY ORDER BY SORT ONLY

8. What's a common risk of using DISTINCT after a JOIN produces extra rows?

The query will fail to run It can hide a deeper problem with the join logic instead of fixing it It permanently deletes duplicate rows from the table There is no risk at all

9. On a very large table, why can DISTINCT become slow?

It often needs to sort or hash the full result set to find duplicates DISTINCT always scans the table twice DISTINCT only works on small tables It rewrites the table on disk

10. For simple single-column deduplication, which other clause often produces the same result as DISTINCT?

HAVING LIMIT GROUP BY UNION ALL

0 / 10

Related Lessons

Continue the Series

Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide)

How to Install XAMPP Step by Step for Beginners

How to Install MySQL Step by Step for Beginners

FAQ

Frequently Asked Questions

1. Does DISTINCT change the original table data?

No. DISTINCT only affects what's shown in your query results — the underlying table and its duplicate rows are never modified or deleted.

2. Can I use DISTINCT with just one column?

Yes — that's the most common use case. SELECT DISTINCT country FROM orders; returns each unique country exactly once.

3. What happens if I use DISTINCT with every column, like SELECT DISTINCT *?

It removes only fully identical rows — every column has to match another row exactly for one copy to be dropped. If even one column differs (like an id), both rows are kept.

4. Is DISTINCT the same as GROUP BY?

For simple deduplication they often produce the same result, but they're built for different purposes — GROUP BY is designed for aggregating data (with functions like SUM() or COUNT()), while DISTINCT is purely for removing duplicate rows.

5. Can I combine DISTINCT with WHERE?

Yes. MySQL applies WHERE first to filter rows, then applies DISTINCT to what's left — so you get unique values from only the filtered results.

6. Does DISTINCT work with calculated columns and aliases?

Yes. You can write SELECT DISTINCT CONCAT(city, ', ', country) AS location FROM orders; and it deduplicates based on the calculated value.

7. Why does my DISTINCT query return more rows than I expected?

This almost always means you're selecting more columns than you intended — remember, DISTINCT compares the full row, so an extra column (even one you don't need in the output) can prevent rows from being merged.

8. Can DISTINCT be used inside an aggregate function?

Yes — COUNT(DISTINCT column), SUM(DISTINCT column), and similar combinations are common and let you calculate against only the unique values.

9. Is DISTINCT case-sensitive?

By default in MySQL, string comparisons are usually case-insensitive (depending on the column's collation), so "Cambodia" and "cambodia" would typically be treated as duplicates and merged into one row.

10. Does the order of columns matter in SELECT DISTINCT col1, col2?

No — the deduplication logic is the same regardless of column order. What matters is which columns are included, not the order they're listed in.

11. Can I use DISTINCT with ORDER BY on a column I didn't select?

In MySQL, you generally can, but it can produce results that feel unpredictable, since you're sorting by a value that isn't part of what made rows unique. It's usually clearer to only sort by columns you've actually selected.

12. Is it ever better to avoid DISTINCT entirely?

Yes — if you find yourself adding DISTINCT purely to patch over duplicate rows from a JOIN, it's worth pausing to check your join conditions first. DISTINCT is best used intentionally, not as an automatic cleanup step.

Part of the CodeMend SQL Module series. Finished Module 3? Move on to Module 4 for filtering rows with WHERE.