How do you handle duplicates with DISTINCT?

Question

Accepted Answer

**`DISTINCT`** removes duplicate rows from a result, returning only unique values. It's used to get distinct values of a column (or combination of columns) — common for finding unique entries or building dropdown lists.

## DISTINCT — unique values

```sql
-- get the unique countries (no duplicates)
SELECT DISTINCT country FROM users;
-- → each country appears once, even if many users share it

-- DISTINCT on multiple columns → unique COMBINATIONS
SELECT DISTINCT country, city FROM users;
-- → each unique (country, city) pair appears once
```

`DISTINCT` applies to all the selected columns together — `DISTINCT country, city` returns unique *combinations*, not unique countries and unique cities separately.

## DISTINCT with COUNT

```sql
-- count how many UNIQUE countries there are
SELECT COUNT(DISTINCT country) FROM users;

-- vs COUNT(*) which counts all rows (with duplicates)
SELECT COUNT(*) FROM users;
```

`COUNT(DISTINCT column)` counts unique values — useful for "how many different X are there?"

## Finding actual duplicate rows

```sql
-- find values that appear MORE THAN ONCE (duplicates)
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;          -- groups with more than one row = duplicates
```

To *find* duplicates (not just remove them), use `GROUP BY` + `HAVING COUNT(*) > 1` — this identifies which values are duplicated and how many times.

## DISTINCT vs GROUP BY

```sql
-- these are often equivalent for getting unique values:
SELECT DISTINCT country FROM users;
SELECT country FROM users GROUP BY country;
-- GROUP BY is more flexible (can add aggregates); DISTINCT is simpler for pure deduplication
```

## A note on performance

```text
DISTINCT requires the database to compare/sort rows to find uniques → can be costly
on large datasets. Often a sign you might restructure the query (e.g. a JOIN producing
duplicates that DISTINCT then removes — consider whether the JOIN is the issue).
```

## Why it matters

`DISTINCT` is a useful, commonly-needed SQL feature for **handling duplicates** — getting unique values is a frequent need (unique categories for a dropdown, distinct entries, counting unique items), so understanding it is practical everyday knowledge.

Knowing how `DISTINCT` works (removing duplicate rows, and that on multiple columns it returns unique *combinations* — a common point of confusion) and how to use `COUNT(DISTINCT)` for counting unique values covers the typical uses.

Equally valuable is knowing how to **find actual duplicates** (using `GROUP BY` + `HAVING COUNT(*) > 1`) — a common, important task for data cleaning and validation (identifying duplicate emails, records, etc.).

Understanding the relationship between `DISTINCT` and `GROUP BY` (often equivalent for deduplication, with GROUP BY being more flexible) and the **performance note** (DISTINCT requires comparing/sorting and can be costly, and sometimes signals a JOIN producing unwanted duplicates) is also useful.

Since handling duplicates — both removing them (DISTINCT) and finding them (GROUP BY/HAVING) — is a common practical need in querying and data quality work, and since understanding DISTINCT's behavior (especially on multiple columns) and its alternatives prevents confusion and inefficiency, knowing how to handle duplicates with DISTINCT is useful, frequently-applied knowledge for working with real data that often contains or requires unique values.