DISTINCT: Unique Values

DISTINCT removes duplicate rows from a result.

SELECT category FROM products;

+-------------+
|  category   |
+-------------+
| Accessories |
| Accessories |
| Displays    |
| Accessories |
| Audio       |
| Video       |
+-------------+

SELECT DISTINCT category FROM products;

+-------------+
|  category   |
+-------------+
| Accessories |
| Displays    |
| Audio       |
| Video       |
+-------------+

Each category now appears once.

DISTINCT applies to the whole row

With several columns, DISTINCT removes rows where every listed column is the same:

SELECT DISTINCT customer_id, status
FROM orders;

+-------------+-----------+
| customer_id |  status   |
+-------------+-----------+
|           1 | delivered |
|           2 | shipped   |
|           3 | delivered |
|           3 | cancelled |
|           4 | pending   |
|           5 | shipped   |
|           1 | pending   |
+-------------+-----------+

Customer 1 appears twice because they have orders with two different statuses — the two delivered orders collapsed into one row.

Counting unique values

COUNT(DISTINCT column) counts how many different values there are:

SELECT COUNT(DISTINCT customer_id) AS customers_with_orders
FROM orders;

+-----------------------+
| customers_with_orders |
+-----------------------+
|                     5 |
+-----------------------+

Common mistakes

• Using DISTINCT to hide duplicates caused by a mistake elsewhere in the query — often a join (Level 2). If you get duplicates you didn't expect, find out why before reaching for DISTINCT.