SELECT DISTINCT (Removing Duplicates)

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.

Learn SELECT DISTINCT (Removing Duplicates) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a…

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.

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

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.

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.

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.

Add the one keyword that turns a repetitive column into a clean list of 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.

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

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 .)

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.

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

Practice quiz

What does SELECT DISTINCT do?

  • Sorts the result
  • Counts the rows
  • Removes duplicate rows from the result
  • Renames a column

Answer: Removes duplicate rows from the result. DISTINCT collapses identical rows so each unique value (or combination) appears once.

DISTINCT decides a row is a duplicate based on what?

  • The whole selected row (all listed columns)
  • Only the first column
  • The id column
  • A random column

Answer: The whole selected row (all listed columns). DISTINCT compares the entire combination of selected columns; a row is a duplicate only if every value matches.

What does SELECT DISTINCT country, city FROM customers; compare?

  • Only country
  • Only city
  • The id
  • The (country, city) pair together

Answer: The (country, city) pair together. It de-duplicates on the pair, so 'France, Paris' and 'France, Lyon' are both kept.

Generally, what happens to the number of distinct rows as you add more columns to SELECT DISTINCT?

  • It tends to decrease
  • It tends to increase (or stay the same)
  • It tends to stay the same
  • It always becomes 1

Answer: It tends to increase (or stay the same). More columns mean more unique combinations, so you usually get more distinct rows, not fewer.

What does COUNT(DISTINCT country) return?

  • The number of unique non-NULL country values
  • The total number of rows
  • The most common country
  • The first country

Answer: The number of unique non-NULL country values. COUNT(DISTINCT col) counts how many different non-NULL values exist.

Under DISTINCT, how are multiple NULL values treated?

  • Each NULL is kept separately
  • NULLs cause an error
  • All NULLs collapse into a single NULL row
  • NULLs are removed entirely

Answer: All NULLs collapse into a single NULL row. For DISTINCT, all NULLs are treated as equal, so multiple NULLs collapse into one.

Where must DISTINCT be placed?

  • At the end of the query
  • Immediately after SELECT
  • After FROM
  • Inside the WHERE clause

Answer: Immediately after SELECT. DISTINCT is a keyword that goes right after SELECT and applies to every column listed.

Does SELECT DISTINCT guarantee the results are sorted?

  • Yes, alphabetically
  • Yes, by id
  • Only for one column
  • No — add ORDER BY if you need a sorted order

Answer: No — add ORDER BY if you need a sorted order. DISTINCT does not sort; add ORDER BY when you need a specific order.

Why is DISTINCT(country), city a trap?

  • It is a syntax error
  • The parentheses are cosmetic; DISTINCT still applies to both columns
  • It de-dupes only country reliably
  • It counts the rows

Answer: The parentheses are cosmetic; DISTINCT still applies to both columns. DISTINCT is not a function; the parentheses don't limit it, so it still covers the whole row.

Will SELECT DISTINCT name, country give you unique names?

  • Yes, names are de-duplicated
  • Only if names repeat
  • No, it gives unique name+country pairs
  • It errors

Answer: No, it gives unique name+country pairs. DISTINCT covers the whole row, so it yields unique name+country combinations, not unique names.