DISTINCT

DISTINCT removes duplicate rows from a result set. ALL is the default and keeps duplicates; DISTINCTROW is a synonym for DISTINCT, and specifying both ALL and DISTINCT is an error. Duplicates usually arrive from a join that multiplies rows, so before reaching for DISTINCT, check whether the join is the real problem.

It applies to the whole row

DISTINCT is a modifier on the select list, not a function on one column. SELECT DISTINCT(a), b is parsed as SELECT DISTINCT a, b — the parentheses are just grouping, and uniqueness is still evaluated across both columns. Adding another column to the select list can therefore increase the number of rows. Two NULLs count as equal for de-duplication, so only one NULL row survives.

The same thing as GROUP BY

The manual treats DISTINCT as a special case of GROUP BY. These two are equivalent, and every GROUP BY optimization applies to both:

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

Cost

MySQL must compare every output row against the ones already emitted, which means using an index that already supplies the order or building a temporary table. Combined with ORDER BY it often needs a temporary table. With LIMIT it can stop as soon as it has found that many unique rows, and in a join it stops scanning a table once a row matches when no column of that table is selected.

-- Which countries have we shipped paid orders to?
-- The join can produce many rows per country, so de-duplicate.
SELECT DISTINCT a.country_code, a.country_name
FROM orders    AS o
JOIN addresses AS a ON a.id = o.ship_address_id
WHERE o.status = 'paid'
  AND o.placed_at >= '2026-01-01'
ORDER BY a.country_name;   -- DISTINCT + ORDER BY commonly needs a temporary table

-- Identical result, written as a grouping. Pick whichever reads better;
-- the optimizer treats them the same.
SELECT a.country_code, a.country_name
FROM orders    AS o
JOIN addresses AS a ON a.id = o.ship_address_id
WHERE o.status = 'paid'
  AND o.placed_at >= '2026-01-01'
GROUP BY a.country_code, a.country_name
ORDER BY a.country_name;

-- 40 rows: one per country.
SELECT DISTINCT country_code FROM addresses;

-- 12,000 rows: every distinct (country, city) pair. DISTINCT covers the whole list.
SELECT DISTINCT country_code, city FROM addresses;

-- Still 12,000 rows. The parentheses do nothing; this is the query above.
SELECT DISTINCT(country_code), city FROM addresses;

-- Works, but joins first and throws duplicates away afterwards.
SELECT DISTINCT c.id, c.email
FROM customers AS c
JOIN orders    AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- Better: ask whether at least one matching row exists. No duplicates are
-- ever produced, so nothing has to be removed, and MySQL can stop at the
-- first matching order per customer.
SELECT c.id, c.email
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
);

-- Row-level de-duplication: unique (customer, product) pairs.
SELECT DISTINCT o.customer_id, oi.product_id
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id;

-- Aggregate-level: one row per customer, counting unique products.
SELECT o.customer_id,
       COUNT(DISTINCT oi.product_id) AS distinct_products,
       GROUP_CONCAT(DISTINCT oi.product_id ORDER BY oi.product_id) AS products
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.customer_id;