SELECT DISTINCT (Removing Duplicates)

Reviewed & published by Brayan K

Real tables are full of repetition — the same country, the same category, the same status appearing hundreds of times. SELECT DISTINCT is how you ask SQL for just the unique values, turning a noisy column into a tidy list.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end of this lesson you'll know exactly what DISTINCT compares, how it behaves across multiple columns, how it handles NULLs, and when to reach for COUNT(DISTINCT ...) instead.

What You'll Learn

• Remove duplicate rows with SELECT DISTINCT
• Understand DISTINCT operates on the whole row
• Count unique values with COUNT(DISTINCT ...)
• How DISTINCT treats NULL values
• When DISTINCT is the right tool vs GROUP BY
• Avoid the DISTINCT(col) syntax trap

Our Sample Table: customers

Every query in this lesson runs against this customers table. Notice how country and tier repeat — that's exactly what DISTINCT is for.

1. The Problem: Duplicate Rows

Imagine the marketing team asks, "Which countries do our customers live in?" If you simply select the country column, you get one row per customer — with all the repeats. That's not a list of countries; it's a list of customers' countries.

It's like emptying a box of business cards onto a table and being asked "which companies are represented?" You don't want every card — you want each company name written down once.

-- The table this lesson uses. Note the repeated countries — that is the
-- point: DISTINCT is what removes them.
CREATE TABLE customers (
    id      INTEGER PRIMARY KEY,
    name    TEXT,
    city    TEXT,
    country TEXT,
    tier    TEXT
);
INSERT INTO customers VALUES
    (1, 'Amélie Dubois', 'Paris',    'France',  'gold'),
    (2, 'Lucas Martin',  'Lyon',     'France',  'silver'),
    (3, 'Mia Chen',      'New York', 'USA',     'gold'),
    (4, 'Jonas Weber',   'Berlin',   'Germany', 'silver'),
    (5, 'Ava Johnson',   'Chicago',  'USA',     'bronze'),
    (6, 'Chloé Bernard', 'Paris',    'France',  'gold'),
    (7, 'Pablo Ruiz',    'Madrid',   'Spain',   'silver'),
    (8, 'Emma Meyer',    'Hamburg',  'Germany', 'gold'),
    (9, 'Noah Lopez',    'Seville',  'Spain',   'bronze');

-- A plain SELECT keeps EVERY row, duplicates and all
SELECT country
FROM customers;

-- ✅ Expected result:
-- country
-- France
-- France
-- USA
-- Germany
-- USA
-- France
-- Spain
-- Germany
-- Spain

2. SELECT DISTINCT — The Fix

Add the keyword DISTINCT immediately after SELECT and the database collapses identical rows into one. The order isn't guaranteed unless you add ORDER BY, but every value will be unique.

-- Uses the customers table from the first example.
-- DISTINCT collapses identical rows into one
SELECT DISTINCT country
FROM customers;

-- ✅ Expected result:
-- country
-- France
-- USA
-- Germany
-- Spain

3. DISTINCT Works on the Whole Row

This is the single most misunderstood thing about DISTINCT: it does not de-duplicate one column at a time. It looks at the entire combination of columns you selected and removes rows where every value matches.

-- Uses the customers table from the first example.
-- DISTINCT looks at the WHOLE selected row, not one column
SELECT DISTINCT country, city
FROM customers;

-- A row is a duplicate only if BOTH columns match.
-- 'France, Paris' and 'France, Lyon' are kept — different cities.
-- country | city
-- France  | Paris
-- France  | Lyon
-- USA     | Austin
-- Germany | Berlin
-- Spain   | Madrid
-- 5 rows

-- ✅ Expected result:
-- country | city
-- France | Paris
-- France | Lyon
-- USA | New York
-- Germany | Berlin
-- USA | Chicago
-- Spain | Madrid
-- Germany | Hamburg
-- Spain | Seville

Your Turn: unique tiers

Add the one keyword that turns a repetitive column into a clean list of unique values.

-- Uses the customers table from the first example.
-- 🎯 YOUR TURN — fill in the keyword, then "Try it Yourself"
-- Goal: list each membership tier only once (no repeats).

SELECT ___ tier        -- 👉 the keyword that removes duplicate rows
FROM customers;

-- ✅ Expected (3 rows): Gold, Silver, Bronze

4. COUNT(DISTINCT ...) — Counting Unique Values

Often you don't want the list of unique values — you want how many there are. Wrapping DISTINCT inside COUNT() answers questions like "how many different countries do we ship to?" in a single number.

-- Uses the customers table from the first example.
-- A classic: how many DIFFERENT countries do we ship to?
SELECT COUNT(DISTINCT country) AS country_count
FROM customers;

-- ✅ Expected result:
-- country_count
-- 4

🧩 Reorder Challenge

The lines below are scrambled. Mentally arrange them so the query counts the distinct countries among Gold-tier customers.

-- Uses the customers table from the first example.
-- These lines are SCRAMBLED. Put them in the right order
-- to count how many distinct countries appear.

FROM customers;
SELECT
WHERE tier = 'Gold'
COUNT(DISTINCT country) AS gold_countries

Why: SQL clauses always run in a fixed written order — SELECT first, then the FROM source, then the WHERE filter. WHERE trims the rows before they reach COUNT(DISTINCT ...), so only Gold customers are counted. (France and Germany have Gold customers → result is 2.)

🧠 Quick Recall

Predict the result before you reveal it. Use the customers table above.

4 — France, USA, Germany, Spain. The three France rows and two USA rows each collapse to one.

5 — the unique pairs are France/Paris, France/Lyon, USA/Austin, Germany/Berlin and Spain/Madrid. The two USA/Austin rows and two France/Paris rows each collapse to one.

3 — there are exactly three tiers (Gold, Silver, Bronze), no matter how many customers hold each one.

Common Errors (and the fix)

• Expecting DISTINCT to de-dupe one column: SELECT DISTINCT name, country does not give unique names — it gives unique name+country pairs. DISTINCT always covers the whole row.
• The DISTINCT(col) trap: writing SELECT DISTINCT(country), city looks like it de-dupes only country, but it doesn't — the parentheses are cosmetic and DISTINCT still applies to both columns. Avoid the function-like syntax.
• Putting DISTINCT in the wrong place: it must come right after SELECT. SELECT country DISTINCT is a syntax error.
• Assuming an order: DISTINCT does not sort. If you need alphabetical output, add ORDER BY country.

Mini-Challenge: Markets & Tiers

Write a single query that lists every unique country + tier combination. The expected shape is in the comments.

-- Uses the customers table from the first example.
-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- 1. Return the distinct (country, tier) combinations
-- 2. ...so we can see which tiers exist in each market.
--
-- ✅ Expected: one row per unique country+tier pairing,
--    e.g. France | Gold , France | Silver , USA | Bronze ...

-- your query here

🎉 Lesson Complete

• ✅ SELECT DISTINCT removes duplicate rows from the result
• ✅ DISTINCT compares the whole selected row, not one column
• ✅ COUNT(DISTINCT col) counts unique non-NULL values
• ✅ Multiple NULLs collapse into a single NULL under DISTINCT
• ✅ Add ORDER BY if you need the unique values sorted
• ✅ Next: control how many rows come back with LIMIT

Practice quiz

Continue this course

• Previous: ORDER BY & Sorting
• Next: LIMIT, OFFSET, TOP & FETCH — Return only the first N rows — and paginate results
• Quick reference: SQL cheat sheet › Filtering & Operators

Frequently asked questions

Does DISTINCT apply to one column or the whole row?

It applies to the whole selected row. SELECT DISTINCT city, country compares the (city, country) pair, so 'Paris, France' and 'Paris, USA' are both kept — they're different rows.

Is SELECT DISTINCT the same as GROUP BY?

For de-duplication they produce the same rows, but GROUP BY is built for aggregation (counting, summing per group). If you only want unique rows and no aggregates, DISTINCT reads more clearly.

Where do I put DISTINCT — it looks like a function?

It is a keyword, not a function. It goes immediately after SELECT and applies to every column you list: SELECT DISTINCT col1, col2. Writing DISTINCT(col1), col2 is a common trap and changes the meaning in some databases.

Does DISTINCT count NULLs as duplicates?

Yes. For DISTINCT purposes all NULLs are treated as equal, so multiple NULL rows collapse into a single NULL in the result — even though NULL = NULL is otherwise never true in SQL.

Will DISTINCT slow my query down?

It can. The database must sort or hash every selected column to find duplicates, which costs CPU and memory on large tables. Only reach for it when you genuinely need unique rows.

Related lessons

• SELECT Statement