SQL SELECT DISTINCT Statement
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT
statement is used to return only distinct
(different) values.
Example
Select all the different countries from the "Customers" table:
SELECT DISTINCT Country FROM Customers;
Try it Yourself »
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
Demo Database
Below is a selection from the Customers table used in the examples:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SELECT Example Without DISTINCT
If you omit the DISTINCT
keyword, the SQL statement
returns the "Country" value from all the records of the "Customers" table:
Count Distinct
By using the DISTINCT
keyword in a function called
COUNT
, we can return the number of different countries.
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
Note: The COUNT(DISTINCT column_name) is not supported in Microsoft Access databases.
Here is a workaround for MS Access:
Example
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
You will learn about the COUNT function later in this tutorial.