SQL SELECT DISTINCT

SQL SELECT DISTINCT returns only unique rows, removing duplicates from the result. A column like Country contains the same value many times; DISTINCT gives you each value once.

Without DISTINCT

First see the problem. This query lists the country of every customer, so Pakistan appears three times.

SELECT Country FROM Customers;

Country
--------
Pakistan
Pakistan
UK
Spain
UAE
UK
Pakistan
USA
USA
Canada
(10 rows)

Using SELECT DISTINCT

Add DISTINCT right after SELECT and each country is shown only once.

SELECT DISTINCT Country
FROM Customers
ORDER BY Country;

Country
--------
Canada
Pakistan
Spain
UAE
UK
USA
(6 rows)

DISTINCT on Multiple Columns

When you list several columns, DISTINCT removes rows where the whole combination is repeated. Here each unique city and country pair appears once.

SELECT DISTINCT Country, City
FROM Customers
WHERE Country IN ('Pakistan', 'UK')
ORDER BY Country, City;

Country  | City
---------+-----------
Pakistan | Islamabad
Pakistan | Karachi
Pakistan | Lahore
UK       | London
UK       | Manchester
(5 rows)

COUNT(DISTINCT ...)

To count how many different values exist, put DISTINCT inside COUNT. Here we count how many different customers have placed orders.

SELECT COUNT(DISTINCT CustomerID) AS buying_customers
FROM Orders;

buying_customers
----------------
7
(1 row)

Frequently asked questions

What is the difference between DISTINCT and GROUP BY?

For simply listing unique values they give the same result. GROUP BY is used when you also want aggregate values, such as a count or sum for each group.

Does DISTINCT treat NULL values as duplicates?

Yes. All NULLs are considered equal for DISTINCT, so at most one NULL row appears in the result.

Is SELECT DISTINCT slow?

It needs to sort or hash the rows to find duplicates, which costs time on large tables. If you find yourself adding DISTINCT to fix a join that returns duplicates, check the join conditions first.