SQL SELECT DISTINCT: Remove Duplicate Rows from Results

SQL Tutorial · Chapter 4 of 32

SQL SELECT DISTINCT returns only the unique values in the columns you select, removing duplicate rows from the result set. It is the quickest way to answer questions such as “which countries do we sell to?” or “which products have ever been ordered?” without listing the same value again and again.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

DISTINCT applies to the whole combination of columns listed, not just the first one. A row is dropped only if every selected column matches another row.

Sample Table

Example 1: Without DISTINCT

SELECT Product FROM Orders;

Six rows come back and Laptop appears twice, once for each order that contains it.

Example 2: With DISTINCT

SELECT DISTINCT Product FROM Orders;

Five unique products. The order of rows is not guaranteed unless you add ORDER BY.

Example 3: Which Customers Have Ordered?

SELECT DISTINCT CustomerID
FROM Orders
ORDER BY CustomerID;

Customer 1 placed two orders but is listed once.

Example 4: DISTINCT on Several Columns

SELECT DISTINCT CustomerID, Product
FROM Orders
ORDER BY CustomerID;

All six rows survive because no two rows share the same CustomerID and Product. DISTINCT works on the combination.

Example 5: Count Unique Values

SELECT COUNT(DISTINCT Product) AS UniqueProducts
FROM Orders;

DISTINCT and NULL

DISTINCT treats all NULL values as equal, so a column with several NULLs returns a single NULL row. COUNT(DISTINCT column) ignores NULLs entirely.

Works in MySQL, SQL Server, PostgreSQL, SQLite

SELECT DISTINCT and COUNT(DISTINCT …) work identically in all four. PostgreSQL adds DISTINCT ON (column) to return the first row per group, which the others do not support; use window functions there instead. SQL Server can also write SELECT DISTINCT TOP 3 ....

How DISTINCT Works Behind the Scenes

To remove duplicates the database must compare every result row with every other. It does this either by sorting the rows so identical ones sit next to each other, or by building a hash table keyed on the selected columns. Both approaches need memory proportional to the number of distinct rows, which is why DISTINCT on a wide column list over a large table can be slow, while DISTINCT on a single indexed column is usually instant: the index already holds the values in order.

A useful habit is to ask why duplicates exist before removing them. If a join multiplies rows because one customer has many orders, the real fix is to aggregate with GROUP BY or to join to a pre-aggregated subquery. DISTINCT then becomes unnecessary and the query is both faster and easier to understand.

Common Mistakes

• Writing SELECT Name, DISTINCT City. DISTINCT must come immediately after SELECT and applies to all columns.
• Expecting DISTINCT to deduplicate one column while other columns still differ; add or remove columns until the combination is what you mean.
• Using DISTINCT to hide a bad join that produces duplicate rows. Fix the join instead.
• Running DISTINCT on huge tables without need; it forces a sort or hash and can be slow.

Practice Exercise

1. List the distinct months in which orders were placed (hint: use the first seven characters of OrderDate).
2. How many different customers have placed an order?

-- 1 (SUBSTR works in MySQL, PostgreSQL and SQLite; SQL Server uses SUBSTRING)
SELECT DISTINCT SUBSTR(OrderDate, 1, 7) AS OrderMonth
FROM Orders
ORDER BY OrderMonth;
-- 2026-01, 2026-02, 2026-03

-- 2
SELECT COUNT(DISTINCT CustomerID) AS Buyers FROM Orders;
-- Buyers = 5

Related Chapters

• SQL SELECT – the basic statement DISTINCT extends
• SQL GROUP BY – unique values with counts and totals
• SQL COUNT, AVG, SUM – aggregate functions
• SQL from Scratch: full course overview

FAQ

What is the difference between DISTINCT and GROUP BY?

Both can return unique combinations of columns. DISTINCT only removes duplicates, while GROUP BY also lets you calculate aggregates such as COUNT or SUM for each group.

Does DISTINCT apply to one column or all columns?

All columns in the SELECT list. Two rows are considered duplicates only when every selected column has the same value.

Is SELECT DISTINCT slow?

It adds a sort or hashing step, so on large tables it costs more than a plain SELECT. On small and medium tables the difference is negligible.

Chapter 4 of 32 · SQL from Scratch: all 32 chapters