SQL DISTINCT across multiple columns: why a value still appears twice

You added DISTINCT, yet Seoul still appears on two rows. SQL did remove duplicates. It compared the combination of columns in SELECT, rather than the single column you were watching.

Which values does DISTINCT compare?

Consider five orders. Orders 1 and 2 share a region and status; order 3 shares the region but has a different status. Each order has a unique id.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders (id, region, status) VALUES
  (1, 'Seoul', 'paid'),
  (2, 'Seoul', 'paid'),
  (3, 'Seoul', 'pending'),
  (4, 'Busan', 'paid'),
  (5, 'Busan', 'pending');

Selecting only the region produces two rows:

SELECT DISTINCT region FROM orders ORDER BY region;
-- Busan
-- Seoul

DISTINCT does not compare every column in the source table. It keeps one copy of each distinct selected result row. With only region selected, duplicate region values collapse.

Why does Seoul appear twice when you select multiple columns?

The three Seoul orders contain two combinations: Seoul / paid and Seoul / pending. Selecting the two columns therefore leaves two Seoul rows, while selecting just region leaves one.

Now select status as well:

SELECT DISTINCT region, status
FROM orders
WHERE region = 'Seoul'
ORDER BY status;
-- Seoul | paid
-- Seoul | pending

Although there is only one region among these orders, there are two distinct (region, status) pairs. DISTINCT does not deduplicate each selected column separately and then recombine them.

If the question is “Which regions have orders?”, select only region. If it is “Which order statuses occur in each region?”, select both. Decide what makes a result row unique before writing the query.

Why might SELECT DISTINCT * change nothing?

SELECT DISTINCT * FROM orders;
-- 5 rows: every id from 1 through 5 is different.

* includes the unique id. Even though orders 1 and 2 share a region and status, their complete rows differ. If you must show an order ID while keeping one representative order per region–status pair, you need a separate rule for which order to choose. DISTINCT alone does not choose that representative.

If a join creates more rows than expected, inspect its join condition and source relationships before adding DISTINCT *. Deduplication can hide a bad join without fixing it.

Use GROUP BY when you need duplicate counts

DISTINCT shows which combinations exist. To see how many rows have each combination, aggregate them:

SELECT region, status, COUNT(*) AS orders_count
FROM orders
GROUP BY region, status
ORDER BY region, status;
-- Busan | paid    | 1
-- Busan | pending | 1
-- Seoul | paid    | 2
-- Seoul | pending | 1

Now the two Seoul / paid orders are visible as a count. Choose between a list of combinations and counts per combination according to the question you need to answer.

Key takeaways

SELECT DISTINCT region deduplicates region values. SELECT DISTINCT region, status deduplicates region–status pairs. Two Seoul rows are expected when their statuses differ. A unique id in * makes all five complete rows distinct; use GROUP BY and COUNT(*) to count rows in each combination.