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.

This chapter of our SQL from Scratch series is for beginners and spreadsheet users moving into databases. You will learn the SELECT DISTINCT syntax, how it behaves on one column versus several, how to count unique values with COUNT(DISTINCT), how NULLs are treated, when GROUP BY is the better choice, and the mistakes that make DISTINCT slow or misleading. Every example uses the same small Orders table, with the exact result shown.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column];

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;

More Practical Variations

DISTINCT with WHERE

The filter runs first, then duplicates are removed. Which products sold for more than 100?

SELECT DISTINCT Product
FROM Orders
WHERE Amount > 100
ORDER BY Product;
-- Laptop, Monitor

Two Laptop orders pass the filter (1200 and 1150) but Laptop is listed once.

DISTINCT on a calculated value

SELECT DISTINCT SUBSTR(OrderDate, 1, 7) AS OrderMonth
FROM Orders
ORDER BY OrderMonth;
-- 2026-01, 2026-02, 2026-03

Use SUBSTRING in SQL Server. This is a fast way to see which periods contain data.

Several unique counts in one query

SELECT COUNT(*)                   AS Orders,
       COUNT(DISTINCT CustomerID) AS Customers,
       COUNT(DISTINCT Product)    AS Products
FROM Orders;
-- Orders = 6, Customers = 5, Products = 5

DISTINCT vs GROUP BY

These two queries return the same five products:

SELECT DISTINCT Product FROM Orders;

SELECT Product FROM Orders GROUP BY Product;

Use GROUP BY as soon as you also need a number per value, for example how many times each product was ordered:

SELECT Product, COUNT(*) AS TimesOrdered
FROM Orders
GROUP BY Product
ORDER BY TimesOrdered DESC;
-- Laptop 2, then Mouse, Monitor, Keyboard, Headset 1 each

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, so the count can be one lower than the number of rows SELECT DISTINCT returns. See SQL NULL values for more on how NULL behaves.

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 .... MySQL accepts COUNT(DISTINCT col1, col2) to count unique combinations; in the other databases, count the rows of a SELECT DISTINCT subquery instead.

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 and Fixes

Tips

• Run SELECT DISTINCT column when you first explore a table to learn which values a column really holds, including typos and stray spaces.
• Combine DISTINCT with LIMIT to preview the first few unique values of a big column.
• Use COUNT(DISTINCT) for dashboards: unique customers, unique products, active days.
• If the same value appears with different capitalisation, clean it with UPPER() or LOWER() inside the SELECT before applying DISTINCT.

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 LIMIT, TOP and FETCH FIRST – return only the first rows
• SQL from Scratch: full course overview

If you also work with unique values in spreadsheets, the same idea appears in Excel’s UNIQUE function and in COUNTA(UNIQUE(…)) for distinct counts. The Excel AI Mastery 100 Formulas course on NextGenTemplates Academy covers these lookup and dynamic-array formulas step by step.

Frequently Asked Questions

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 and filter groups with HAVING.

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, and an index on the column helps a lot.

How do I count distinct values in SQL?

Use COUNT(DISTINCT column). For example SELECT COUNT(DISTINCT CustomerID) FROM Orders returns how many different customers have ordered. NULLs are not counted.

Does SELECT DISTINCT remove NULL values?

No. All NULLs are treated as one value, so a single NULL row is kept. Only COUNT(DISTINCT) ignores NULLs.

Can I use DISTINCT on just one column but show other columns?

Not with plain DISTINCT. Use GROUP BY with an aggregate (such as MAX) for the other columns, PostgreSQL’s DISTINCT ON, or ROW_NUMBER() to keep one row per value.

Wrapping Up

SELECT DISTINCT is the simplest way to see the unique values in your data. Remember that it works on the full combination of selected columns, that it does not sort, and that COUNT(DISTINCT) ignores NULLs. When you need totals or counts per value, move on to GROUP BY. Try the practice exercise above, then continue with the SQL WHERE clause chapter.

📅 Last updated: October 2026

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