How to Use the MySQL DISTINCT Clause
In this tutorial, we'll learn how to use the MySQL DISTINCT. DISTINCT is used with the SELECT statement to select only different (distinct) values a table in a database.
We can use the DISTINCT clause with the COUNT() function to count only the number of the distinct values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2,..., columnN
FROM table;
COUNT DISTINCT Syntax
SELECT COUNT(DISTINCT column)
FROM table;
MySQL SELECT DISTINCT Examples
Below is the our demo customers table.
| customer_id | customer_code | customer_name | country |
|---|---|---|---|
| 1 | #CID_90001 | Josh Warner | Japan |
| 2 | #CID_90002 | Lisa Ramsey | South Korea |
| 3 | #CID_90003 | Clifton Christensen | Japan |
| 4 | #CID_90004 | Iris Wilkerson | United State |
| 5 | #CID_90005 | Charles Harmon | Japan |
| 6 | #CID_90006 | Glen Wood | United State |
| 7 | #CID_90007 | Francis Lewis | Germany |
| 8 | #CID_90008 | William Barnett | China |
| 9 | #CID_90008 | Iris Wilkerson | Australia |
| 10 | #CID_90010 | Lisa Ramsey | Australia |
Example 1: Select All Records
In this example, we write a statement select all countries, including the duplicate values, from the customers table.
Let's look at the statement below:
SELECT c.country
FROM customers c;
Result:
| country |
|---|
| Japan |
| South Korea |
| Japan |
| United State |
| Japan |
| United State |
| Germany |
| China |
| Australia |
| Australia |
As we can see, there are duplicate values in the above result set, because the SELECT statement allows duplicate values.
Example 2: Select Only Distinct Records
In this example, we write a statement to select all countries but remove the duplicates from the result set.
The following statement select only the distinct country from the customers table:
SELECT DISTINCT c.country
FROM customers c;
Result:
| country |
|---|
| Japan |
| South Korea |
| United State |
| Germany |
| China |
| Australia |
As we can see, there are no duplicate values in the above result set. In our demo customers table, there are 10 records, but there are only 6 records in the result set.
Example 3: Count the Number of Distinct Records
The following statement counts the number of distinct countries in the customers table:
SELECT COUNT(DISTINCT c.country)
FROM customers c;
If we don't use the DISTINCT with the COUNT() function in the above statement, MySQL returns 10, but this statement returns only 6, because when using the DISTINCT with the COUNT function, MySQL counts only the distinct number of values.
In this tutorial, we've learned how to use the MySQL DISTINCT. We use DISTINCT with the SELECT statement to select only records that have different (distinct) values. The DISTINCT clause can be used with the COUNT() function to count only the number of the distinct values.