DISTINCT

Many questions are about which different values exist: which countries do customers come from, which categories do we sell? SELECT DISTINCT removes duplicate rows from a result.

Removing duplicates

SELECT country FROM customers;

SELECT DISTINCT country FROM customers;

Ten customers, but only six different countries.

DISTINCT across several columns

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

DISTINCT applies to the whole row, not just the first column: two rows are duplicates only if every selected column matches. Ana and Julia both live in Lisbon, Portugal, so that pair appears once.

DISTINCT and NULL

SELECT DISTINCT city
FROM customers
WHERE country = 'France';

For DISTINCT, all NULLs count as the same value, so at most one NULL row appears.

Counting distinct values

SELECT COUNT(country) AS customer_rows,
       COUNT(DISTINCT country) AS countries
FROM customers;

Inside an aggregate function, DISTINCT makes each value count once (lesson 18 covers COUNT fully).

SELECT COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;

Ten customers exist, but only nine have placed an order.

DISTINCT vs GROUP BY

SELECT category FROM products GROUP BY category;

Grouping produces the same list as DISTINCT. Use DISTINCT when you just want unique values, and GROUP BY when you also want to calculate something per group (lesson 19).

Don't use DISTINCT to hide mistakes

If a query unexpectedly returns duplicates, the cause is usually a join that matches more rows than you expected. Adding DISTINCT hides the symptom but makes the query slower and can hide real bugs. Find and fix the join instead (lesson 27).

Try it yourself

Which order statuses are in use, and which products have ever been ordered?