MySQL DISTINCT

The DISTINCT keyword removes duplicate rows from a query result. When multiple rows contain the same value in a column, DISTINCT returns each unique value only once. It is used with the SELECT statement.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

Why DISTINCT is Needed

Without DISTINCT, if 100 orders exist and 5 of them are for the same customer, querying the customer column returns 5 entries for that customer. With DISTINCT, that customer appears only once.

Sample Table: orders

+----------+----------+--------+-----------+
| order_id | customer | city   | status    |
+----------+----------+--------+-----------+
|        1 | Alice    | Delhi  | delivered |
|        2 | Bob      | Mumbai | pending   |
|        3 | Alice    | Delhi  | cancelled |
|        4 | Carol    | Delhi  | delivered |
|        5 | Bob      | Mumbai | delivered |
|        6 | Carol    | Chennai| pending   |
+----------+----------+--------+-----------+

Without DISTINCT

SELECT customer FROM orders;

Output:

+----------+
| customer |
+----------+
| Alice    |
| Bob      |
| Alice    |
| Carol    |
| Bob      |
| Carol    |
+----------+

Alice, Bob, and Carol appear multiple times because they each have more than one order.

With DISTINCT

SELECT DISTINCT customer FROM orders;

Output:

+----------+
| customer |
+----------+
| Alice    |
| Bob      |
| Carol    |
+----------+

Each customer appears only once, regardless of how many orders they placed.

DISTINCT on Multiple Columns

When DISTINCT is applied to multiple columns, it returns unique combinations of those columns — not unique values from each column independently:

SELECT DISTINCT customer, city FROM orders;

Output:

+----------+---------+
| customer | city    |
+----------+---------+
| Alice    | Delhi   |
| Bob      | Mumbai  |
| Carol    | Delhi   |
| Carol    | Chennai |
+----------+---------+

Carol appears twice because she placed orders from two different cities — Delhi and Chennai. The combination (Carol, Delhi) is different from (Carol, Chennai).

DISTINCT with WHERE

Find all unique cities where delivered orders were placed:

SELECT DISTINCT city
FROM orders
WHERE status = 'delivered';

DISTINCT with COUNT

Count how many unique customers placed orders:

SELECT COUNT(DISTINCT customer) AS unique_customers
FROM orders;

Output:

+-----------------+
| unique_customers|
+-----------------+
|               3 |
+-----------------+

DISTINCT vs GROUP BY

In many cases, DISTINCT and GROUP BY produce similar results when used on a single column. However, GROUP BY is more powerful because it can also apply aggregate functions (like COUNT, SUM) to groups. Use DISTINCT for simple deduplication and GROUP BY when aggregation is needed.

Key Points

• SELECT DISTINCT column returns each unique value in that column only once.
• When applied to multiple columns, DISTINCT considers the combination of all specified columns.
• DISTINCT can be combined with COUNT to count unique values.
• It does not modify the table — it only affects the displayed results.
• For more advanced grouping with aggregation, GROUP BY is the appropriate tool.