SQL SELECT DISTINCT: What It Actually Deduplicates, and When It Hides a Bug

SELECT DISTINCT removes duplicate projected rows. It does not pick one row based on a single column, repair a multiplied join, or guarantee which full row survives.

The select list defines the deduplication key. Add a unique column such as order_id, and every projected row becomes distinct again. Start by naming the grain you want, then choose the tool that produces that grain explicitly.

DISTINCT works on the complete select list

This query asks for unique customer-region combinations. Acme appears twice because it ordered in two regions. The two Hooli rows collapse because both projected values match, including the NULL region.

Input: orders

SELECT DISTINCT customer, region
FROM orders
ORDER BY customer, region NULLS LAST;

Output

Adding order_id would return all eight rows because that column is unique. Also note the NULL behavior: duplicate NULL values collapse in a distinct result even though NULL = NULL is not true in a predicate. The SQL order-of-operations guide explains where projection, deduplication, and sorting sit in one query block.

COUNT(DISTINCT) and GROUP BY answer different questions

COUNT(DISTINCT column) counts unique non-NULL values. That differs from counting rows returned by SELECT DISTINCT column, which can include one NULL row.

Input: orders

SELECT
  COUNT(*) AS order_rows,
  COUNT(region) AS rows_with_region,
  COUNT(DISTINCT region) AS distinct_known_regions,
  COUNT(DISTINCT customer) AS distinct_customers
FROM orders;

Output

For more count-specific edge cases, see SQL COUNT.

Use GROUP BY when the unique keys need measures or a group filter. The result has one row per region, just as a distinct region list would, but each row remains an addressable group for its counts.

Input: orders

SELECT
  region,
  COUNT(*) AS order_rows,
  COUNT(DISTINCT customer) AS customer_count
FROM orders
GROUP BY region
ORDER BY region NULLS LAST;

Output

The GROUP BY guide covers aggregate and HAVING decisions. Do not make a blanket performance claim between DISTINCT and GROUP BY; PostgreSQL can choose different plans as data, indexes, and projections change.

DISTINCT ON chooses one complete row per key

PostgreSQL's DISTINCT ON keeps the first row in each ordered key group. The ORDER BY must start with the distinct key; later expressions define the winner. Here the latest order wins, and order_id DESC resolves same-date ties.

Input: orders

SELECT DISTINCT ON (customer)
  customer,
  order_id,
  order_date,
  amount
FROM orders
ORDER BY customer, order_date DESC, order_id DESC;

Output

DISTINCT ON is PostgreSQL-specific. A ROW_NUMBER() partition with the same ordering expresses the same choice when another engine is required. Ordinary SELECT DISTINCT customer, order_id, ... cannot solve this because every order ID is already different.

DISTINCT cannot repair join fanout

A one-to-many join repeats the parent order amount for every item. This audit displays both common patches: summing the repeated amount inflates revenue, while SUM(DISTINCT amount) collapses two legitimate 100.00 orders for customer 1.

Input: customer_orders

Input: order_items

SELECT
  o.customer_id,
  COUNT(*) AS joined_rows,
  COUNT(DISTINCT o.order_id) AS distinct_orders,
  SUM(o.amount) AS duplicated_amount,
  SUM(DISTINCT o.amount) AS distinct_amount
FROM customer_orders AS o
JOIN order_items AS i
  ON i.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

Output

Customer 1's correct order revenue is 200.00, which appears in neither sum. If the item table is used only to require that an order has at least one item, EXISTS preserves one row per order and prevents fanout.

Input: customer_orders

Input: order_items

SELECT
  o.customer_id,
  COUNT(*) AS order_count,
  SUM(o.amount) AS order_revenue
FROM customer_orders AS o
WHERE EXISTS (
  SELECT 1
  FROM order_items AS i
  WHERE i.order_id = o.order_id
)
GROUP BY o.customer_id
ORDER BY o.customer_id;

Output

If child measures are also needed, aggregate the child table to one row per order before joining. The SQL joins guide covers outer joins, membership tests, and fanout decisions.

A practical decision guide

Treat an unexplained final DISTINCT as a review signal. It may be correct, but first ask which columns define the intended grain and which earlier operation created duplicates. Removing visible duplicates is not the same as fixing their cause.

FAQ

Does DISTINCT apply to one column or every selected column?

It applies to the complete projected row. SELECT DISTINCT a, b returns unique (a, b) combinations. Parentheses around a do not make it a one-column distinct operation.

Does DISTINCT keep one NULL?

Yes. Duplicate projected rows containing NULL collapse together. This is separate from predicate equality, where NULL = NULL is unknown.

What is the difference between UNIQUE and DISTINCT?

UNIQUE is a schema constraint that controls stored data. DISTINCT is a query operation that removes duplicate rows from one result. Their NULL behavior can also differ by engine and constraint definition.

Is DISTINCT slower than GROUP BY?

There is no useful universal answer. The projected columns, aggregates, indexes, data distribution, memory, and selected plan matter. Use the operation that states the query's job, then inspect the plan for the real workload.

When is COUNT(DISTINCT) appropriate after a join?

It is appropriate when the metric is genuinely unique entities across the joined rows, such as unique customers per product. It is not a general repair for duplicated sums or an unclear join grain.