MySQL Distinct: Remove Duplicate Rows from Results

DISTINCT removes duplicate rows from a result.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

Without it, a column with repeated values repeats them:

query.sql

SQL

SELECT category FROM products;

Output

OUTPUT

+------------+
| category   |
+------------+
| stationery |
| stationery |
| bags       |
| stationery |
| home       |
| home       |
| bags       |
| stationery |
+------------+
8 rows in set

With DISTINCT, each category appears once:

query.sql

SQL

SELECT DISTINCT category FROM products;

Output

OUTPUT

+------------+
| category   |
+------------+
| stationery |
| bags       |
| home       |
+------------+
3 rows in set

Several columns

DISTINCT applies to the whole row of selected columns, not to the first column:

query.sql

SQL

SELECT DISTINCT category, stock > 0 AS in_stock FROM products ORDER BY category, in_stock;

Output

OUTPUT

+------------+----------+
| category   | in_stock |
+------------+----------+
| bags       |        1 |
| home       |        1 |
| stationery |        0 |
| stationery |        1 |
+------------+----------+
4 rows in set

Each combination of the two columns appears once. stationery is listed twice, because it has products both in stock and out of stock.

Counting distinct values

query.sql

SQL

SELECT COUNT(*) AS products, COUNT(category) AS with_category, COUNT(DISTINCT category) AS categories FROM products;

Output

OUTPUT

+----------+---------------+------------+
| products | with_category | categories |
+----------+---------------+------------+
|        8 |             8 |          3 |
+----------+---------------+------------+
1 row in set

COUNT(DISTINCT column) counts the different non-NULL values. This answers questions such as “How many different customers ordered this month?”.

NULL in DISTINCT

For DISTINCT, all NULL values are treated as the same value, and they collapse into one row:

query.sql

SQL

1
2
3

CREATE TABLE contacts (id INT, phone VARCHAR(15));
INSERT INTO contacts VALUES (1, '98450'), (2, NULL), (3, NULL), (4, '98450');
SELECT DISTINCT phone FROM contacts ORDER BY phone;

Output

OUTPUT

+-------+
| phone |
+-------+
| NULL  |
| 98450 |
+-------+
2 rows in set

COUNT(DISTINCT phone) ignores NULL and returns 1.

DISTINCT vs GROUP BY

These two give the same list of values:

query.sql

SQL

SELECT category FROM products GROUP BY category ORDER BY category;

Output

OUTPUT

+------------+
| category   |
+------------+
| bags       |
| home       |
| stationery |
+------------+
3 rows in set

Use DISTINCT when you just want unique values. Use GROUP BY when you also want to calculate something for each group (a count, a sum) (GROUP BY).

Costs and cautions

DISTINCT needs MySQL to compare rows, often with a temporary table or an index. On a large table with no index on the column, it can be slow.
It is not a fix for a bad join. If a join returns duplicates, find out why (usually a missing join condition or a one-to-many relation), rather than hiding it with DISTINCT.
DISTINCT and ORDER BY together: the ORDER BY columns must appear in the select list.
DISTINCT is not a function. SELECT DISTINCT(category), price still applies to both columns, because the brackets are only brackets.

query.sql

SQL

SELECT DISTINCT(category), stock > 0 AS in_stock FROM products ORDER BY category, in_stock LIMIT 3;

Output

OUTPUT

+------------+----------+
| category   | in_stock |
+------------+----------+
| bags       |        1 |
| home       |        1 |
| stationery |        0 |
+------------+----------+
3 rows in set

warning

DISTINCT as a bandage

If you add DISTINCT to make duplicates disappear without knowing why they appeared, you have likely hidden a wrong join. Fix the query, not the symptom.