SQL DISTINCT Explained for Beginners (Remove Duplicates)

SQL DISTINCT removes duplicate rows from your query results, so each value shows up only once. If you have ever run a query and seen the same city or name listed again and again, DISTINCT is the fix. In this beginner guide you will learn what SQL DISTINCT is, how to use it with one or many columns, and how to count unique values.

You do not need any experience. We define every term as we go, and every example is small enough to copy and run.

What does SQL DISTINCT do?

A database stores data in tables. A table has columns (the labels, like name or city) and rows (one record each). A query is a question you ask the database. Sometimes the answer contains the same value many times. DISTINCT tells the database: "Show me each different value only once."

Think of a class register where students write their home city. Ten students may write "Paris". If you ask for the list of cities, you want Paris once, not ten times. That is DISTINCT.

Our example table

We will use a small table called customers. Here is what it contains:

Notice that Paris appears three times and London twice. Let us see what happens without DISTINCT first.

Step 1: A normal SELECT shows duplicates

SELECT city
FROM customers;

SELECT chooses which columns to show. FROM says which table to read. The result is six rows: Paris, London, Paris, Berlin, London, Paris. The repeats are not helpful if you only want to know which cities you have customers in.

Step 2: Add DISTINCT to remove duplicates

SELECT DISTINCT city
FROM customers;

The only change is the word DISTINCT right after SELECT. Now the result has three rows: Paris, London, Berlin. Each city appears once. The original table is not changed. DISTINCT only affects what you see in the result.

Step 3: DISTINCT with more than one column

You can put several columns after DISTINCT. The database then removes rows where the whole combination is the same.

SELECT DISTINCT city, country
FROM customers;

This returns Paris/France, London/UK and Berlin/Germany. A row is only removed when both city and country match an earlier row. This matters: DISTINCT applies to the full list of columns, not just the first one. It is not possible to make only city unique while still showing other columns that differ.

Step 4: Count unique values with COUNT(DISTINCT)

Often you do not want the list. You want a number: how many different cities do we have? An aggregate function is a function that turns many rows into one value. COUNT is one of them. Combine it with DISTINCT like this:

SELECT COUNT(DISTINCT city) AS total_cities
FROM customers;

COUNT(DISTINCT city) counts each different city once, so the answer is 3. The word AS gives the result column a friendly name, total_cities. Without DISTINCT, COUNT(city) would return 6, because it counts every row.

Step 5: DISTINCT with WHERE and ORDER BY

DISTINCT works well with other parts of a query. WHERE filters rows, and ORDER BY sorts the result.

SELECT DISTINCT city
FROM customers
WHERE country = 'France'
ORDER BY city;

Line 1 asks for unique cities. Line 2 reads the customers table. Line 3 keeps only customers in France. Line 4 sorts the cities from A to Z. The database first filters, then removes duplicates, then sorts. Here the result is just Paris.

DISTINCT and NULL

NULL means "no value" in SQL. If several rows have NULL in a column, DISTINCT treats them as the same and shows a single NULL row. That is handy to know when a column has missing data.

Quick summary

Common beginner mistakes

• Writing DISTINCT in the middle of the column list. It must come right after SELECT.
• Thinking DISTINCT only affects the first column. It applies to every column you list.
• Using DISTINCT to hide a bad query. If you see surprising duplicates after a JOIN, check your join first.
• Using DISTINCT on huge tables when you do not need it. It makes the database do extra work.

The same SELECT DISTINCT syntax works in MySQL, PostgreSQL, SQL Server and SQLite, so what you learn here carries over.