Lesson 9 of 26 · see the practice database

Unique values with DISTINCT

Tables repeat values. Many customers live in the same city. Many products share a category. When you want to know which values exist, not every row that has them, DISTINCT removes the repeats.

The problem

SELECT category FROM products;

Sixteen products give sixteen rows, with each category repeated again and again.

The cure

Put DISTINCT straight after SELECT.

SELECT DISTINCT category FROM products;

Now each category appears once. The question changed from "what is the category of each product?" to "what categories are there?".

SELECT DISTINCT city FROM customers ORDER BY city;

DISTINCT does not sort the result. Add ORDER BY if you want an order.

DISTINCT covers the whole row

With more than one column, DISTINCT looks at the combination. A row is removed only if every one of its columns matches another row.

SELECT DISTINCT state, city
FROM customers
ORDER BY state, city;

A state appears several times here, once for each of its cities. That is correct: the pairs are different.

This is the point people get wrong. DISTINCT is not a function that you wrap around one column. SELECT DISTINCT(state), city means exactly the same as the query above. The brackets do nothing. You cannot make one column unique and leave the others free. If you want one row for each state with something chosen from its cities, that is a job for GROUP BY.

Counting unique values

How many different cities do the customers come from? Put DISTINCT inside COUNT.

SELECT COUNT(DISTINCT city) AS cities,
       COUNT(city) AS rows_with_a_city
FROM customers;

The first number counts each city once. The second counts every row. Comparing the two is a quick test for repeats in a column.

How many customers have ever placed an order?

SELECT COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;

With a filter

WHERE runs first, then the repeats are removed.

SELECT DISTINCT status
FROM orders
WHERE order_date >= '2026-06-01';

Which statuses have appeared since June?

NULL counts as a value here

For DISTINCT, all the NULLs are treated as the same, and one NULL row is kept.

SELECT DISTINCT manager_id FROM employees ORDER BY manager_id;

But COUNT(DISTINCT manager_id) leaves NULL out, like every count of a column.

Which products have ever been ordered?

SELECT DISTINCT product_id
FROM order_items
ORDER BY product_id;

A product that was bought many times appears once. Compare the list with the sixteen product ids and you can see which product nobody has ordered. A later lesson finds it properly with a join.

Do not use DISTINCT to hide a mistake

When a query that joins tables returns rows twice, it is tempting to add DISTINCT and move on. Usually the doubling means that the join is wrong, and DISTINCT hides the fault while leaving totals and counts wrong elsewhere. When you see duplicates you did not expect, find out why before reaching for DISTINCT.

It also costs time. To remove repeats, the database has to sort or otherwise compare all the rows. On a large result that is real work, so use DISTINCT when you need it and not as a habit.

DISTINCT or GROUP BY?

These two return the same rows:

SELECT DISTINCT category FROM products;

SELECT category FROM products GROUP BY category;

Use DISTINCT when you only want the list of values. Use GROUP BY when you also want a figure for each value, such as how many products each category has. That starts in the lesson after next.

Where DISTINCT sits in a query

It belongs with SELECT, before the first column. The full order is now: SELECT DISTINCT, FROM, WHERE, ORDER BY, LIMIT.

Try it yourself

SELECT DISTINCT department FROM employees ORDER BY department;

SELECT DISTINCT state FROM customers ORDER BY state;

SELECT COUNT(DISTINCT product_id) FROM order_items;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.