SQL-01-005-P · Primer — Removing Duplicates with DISTINCT

The idea in one sentence

DISTINCT looks at the rows your SELECT is about to return and throws away the repeats, so you get back each unique value — or each unique combination of values — exactly once.

Why it exists

A table almost never holds tidy, one-of-each data. An orders table has one row per order, so the same customer shows up ten times and the same city shows up thousands of times. That is correct — each order is a real event — but it means a plain SELECT city FROM orders hands you the city over and over, once for every order placed there. When the question you actually have is "which cities have we ever shipped to?", that wall of repeats is noise, not an answer.

DISTINCT exists to answer that class of question directly: which values actually occur here, ignoring how many times each one occurs. Instead of reading a printout and mentally crossing out duplicates, you tell the database to do it. It is the difference between "list every order's city" and "list the cities" — the second is a question about unique values, and DISTINCT is the word for it.

How it works

You place DISTINCT immediately after SELECT, and it applies to the whole column list that follows it — not to one column, but to the entire row the query produces. The database builds each output row from your SELECT list, then keeps only the first occurrence of each unique row, discarding every later duplicate. With one column, "unique row" just means "unique value in that column." With two or more columns, a row counts as a duplicate only when it matches another row in every selected column — this is multi-column DISTINCT, and it finds unique combinations, not unique values per column. Because deduplication happens after the columns are chosen, the result depends entirely on which columns you list. Here is the whole idea on a tiny orders table:

CREATE TABLE orders (customer TEXT, city TEXT);
INSERT INTO orders VALUES
  ('Ada', 'Paris'),
  ('Ada', 'Paris'),
  ('Ben', 'Paris'),
  ('Ben', 'Lyon'),
  ('Ada', 'Paris');

-- One column: unique VALUES
SELECT DISTINCT city FROM orders;
-- city
-- -----
-- Paris
-- Lyon

-- Two columns: unique COMBINATIONS
SELECT DISTINCT customer, city FROM orders;
-- customer | city
-- ---------+------
-- Ada      | Paris
-- Ben      | Paris
-- Ben      | Lyon

Five rows went in; DISTINCT city returned 2 (the cities), and DISTINCT customer, city returned 3 (the who-shipped-where pairs).

The one gotcha

DISTINCT always applies to the entire SELECT list — it is never a per-column filter. New learners read SELECT DISTINCT customer, city as "distinct customers, plus their city," expecting one row per customer. It is not that. It means "distinct (customer, city) pairs," so Ben correctly appears twice — once for Paris, once for Lyon — because those are two different combinations. Add a column that varies, like an order_id, and every row becomes unique, so DISTINCT quietly removes nothing at all. So the rule is: the more columns you list after DISTINCT, the less it collapses, because each extra column gives rows one more way to differ. If you truly want "one row per customer" plus a summary of their cities, that is a GROUP BY job, not a DISTINCT one.

See it — how DISTINCT collapses rows by the columns you select

flowchart TD
    A["5 order rows<br/>(customer, city)"] --> B{"Which columns<br/>after DISTINCT?"}
    B -->|"DISTINCT city"| C["Compare rows<br/>by city only"]
    B -->|"DISTINCT customer, city"| D["Compare rows by<br/>the full pair"]
    C --> E["Keep first of<br/>each unique value"]
    D --> F["Keep first of each<br/>unique combination"]
    E --> G["2 rows:<br/>Paris, Lyon"]
    F --> H["3 rows: Ada-Paris,<br/>Ben-Paris, Ben-Lyon"]

The one-line takeaway

DISTINCT collapses duplicate rows to their unique values across exactly the columns you list after it — one column gives unique values, several give unique combinations.

Where you'll meet it

You'll meet DISTINCT the very first time you need a clean pick-list: the unique cities for a dropdown, the distinct statuses an order can be in, the set of countries you actually operate in. You'll reach for COUNT(DISTINCT column) to answer "how many different customers ordered this month?" versus COUNT(*)'s "how many orders?" — a pairing interviewers love because the two numbers are almost never equal. And you'll hit its one gotcha when a SELECT DISTINCT mysteriously returns duplicates and the culprit turns out to be a column you forgot was in the list, making every row unique. Recognizing that DISTINCT deduplicates on the whole row — and knowing to switch to GROUP BY when you want per-group summaries instead of raw unique rows — is one of the first real forks in the road of writing SQL that answers the question you meant to ask.

Named concepts: DISTINCT keyword, unique row values, multi-column DISTINCT