DISTINCT helps you find out the distinct or unique values from a table. This is very helpful as it omits the duplicate values from the result-set and returns only the distinct values.
Suppose you are at an international conference. You have been informed that the conference has 100 guests. There are 20 guests each from India, Japan, United States, Australia, and Canada. So you can say that the conference has guests from 5 different countries. Now, if you make a table of the above, you’ll see that there would be many duplicate values in the Country column. This is where, leveraging the MYSQL DISTINCT function will help clear up the cluttered data by only displaying one value each.
Syntax of MySQL DISTINCT
We use the DISTINCT Statement along with the SELECT statement. The syntax for the
DISTINCT clause is as follows:
SELECT DISTINCT expressions FROM table_name;
Examples of the DISTINCT Clause
Let us consider the following ConferenceGuests table.
1. DISTINCT Clause for a Single Column
When passing a single column as expression, the
DISTINCT clause returns all the unique values in that column. Suppose you want to find the values of distinct countries in the above table. Then you will use the following query:
SELECT DISTINCT Country FROM ConferenceGuests;
The output will be the following:
As you can see, the query returns the unique values in the Country column and omits the duplicate values.
2. DISTINCT Clause for Multiple Columns
Now if you see the ConferenceGuests table, you see that there are many guests from the state of Queensland in Australia. Furthermore, you can also see two guests from the Indian state of Maharashtra.
If we specify multiple columns, State and Country in this case, to the
DISTINCT clause then we can retrieve the unique combinations for those columns from the table. The query for this would be:
SELECT DISTINCT State, Country FROM ConferenceGuests;
The output will be:
Wait, what is that last state (rather a prefecture) entry for Japan? It may have happened that Sakura missed out filling the state (or prefecture) name in the conference form. This must have resulted in the value being NULL in our table.
What is this NULL value? A NULL value means that the field has no value present. Since Sakura forgot to mention her state’s name, the table assigned the State’s value as NULL by default.
So why did the
DISTINCT clause retrieve the NULL value as a unique value? Well, the DISTINCT clause does not ignore the NULL values in a table and hence, we get the NULL value in our result-set.
3. DISTINCT Clause to Count Distinct Values
DISTINCT Clause coupled with the
count() function can be a powerful tool. Using the
count() function in the expression for the
DISTINCT clause syntax, we can calculate the total number of unique values.
Let’s try to find out the total number of countries in the ConferenceGuests table. For this, we will use the following query:
SELECT count(DISTINCT Country) FROM ConferenceGuests;
What happens here is that the
DISTINCT Country’ parameter retrieves the unique values from the Country column. These values are passed to the
count() function which counts the number of unique values and returns the result-set as follows:
So yes, there were five countries in that table – United States, India, Japan, Australia, and Canada.
4. DISTINCT Values with Count
Now, how about finding the total number of guests from each country? For this, we will be using the
GROUP BY clause along with the
DISTINCT clause and the
SELECT DISTINCT Country, Count(*) FROM ConferenceGuests GROUP BY Country;
You will get the following output:
That is all for the MySQL
DISTINCT clause. If you wish to do further reading, check out the articles mentioned in the References section below.