SQL DISTINCT: remove duplicates

When you display the city of every customer, the same city comes back dozens of times. To get the list of distinct values, you add DISTINCT right after SELECT.

Syntax

SELECT DISTINCT column1, column2
FROM table;

How it works

DISTINCT removes duplicate rows from the result. Important point: it applies to the whole row, not to a single column. With SELECT DISTINCT ville, age, two rows are only duplicates if the city and the age are identical.

Counting distinct values

COUNT(DISTINCT column) counts the number of distinct values in a column, ignoring NULLs.

DISTINCT or GROUP BY?

SELECT DISTINCT ville FROM clients and SELECT ville FROM clients GROUP BY ville give the same result. Use DISTINCT to remove duplicates, and GROUP BY when you also want to compute something per group (a count, a sum…).

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT DISTINCT ville
FROM clients
ORDER BY ville;

SELECT COUNT(DISTINCT ville) AS villes, COUNT(*) AS clients
FROM clients;

SELECT DISTINCT departement, poste
FROM employes
ORDER BY departement;

Common mistakes

SELECT DISTINCT id, ville FROM clients;

SELECT DISTINCT ville FROM clients;

SELECT prenom, DISTINCT ville FROM clients;

SELECT DISTINCT ville FROM clients;

SELECT DISTINCT c.nom FROM clients c JOIN commandes o;

SELECT DISTINCT c.nom FROM clients c JOIN commandes o ON o.client_id = c.id;

Exercises

Try writing the query yourself before opening the solution.

SELECT DISTINCT statut FROM commandes;

SELECT COUNT(DISTINCT client_id) FROM commandes;

Frequently asked questions