Removing Duplicates with DISTINCT

Explanation

Tables often have repeated values in a column. In the library dataset, the books table might have dozens of books but only a handful of distinct genres:

If you run SELECT genre FROM books, you'll get Fiction, History, Fiction, History — one row per book, with genres repeating. Often what you actually want is the list of unique genres. That's exactly what DISTINCT gives you.

Syntax

SELECT DISTINCT column_name FROM table_name;

DISTINCT goes right after SELECT and applies to the whole list of columns that follows it:

SELECT DISTINCT genre FROM books;

Interactive Example

Common Mistakes

• Thinking DISTINCT only removes exact full-row duplicates. It removes duplicates based on the columns you selected. SELECT DISTINCT genre will collapse every row with the same genre into one, even if the books differ in every other column.
• Using DISTINCT when you meant to count something. DISTINCT shows you unique values, but if you want to know how many unique values there are, you'll eventually pair it with COUNT — a topic covered in a later lesson.
• Applying DISTINCT to the wrong column. SELECT DISTINCT title from books will show every title as unique (assuming no duplicate titles), which isn't useful if you actually wanted unique genres.

Best Practices

• Use DISTINCT to sanity-check a column you're unfamiliar with — it's a fast way to see what values actually show up in real data.
• Be intentional about which columns follow DISTINCT — remember it applies to the combination of all listed columns, not each one independently.
• Don't reach for DISTINCT as a fix for a query that's returning duplicate rows unexpectedly — first check whether your WHERE clause or table structure is the real cause.

Practice Question

Using the second playground above, write a query that returns the distinct list of countries found in the publishers table (note: publishers, not authors, though both have a country column).

Summary

DISTINCT removes duplicate values from your query results, letting you see the unique set of values in one or more columns. It's a read-only view of the data — it never changes what's actually stored in the table.

FAQ