MySQL DISTINCT Clause – How to Display Unique Values from a MySQL Database?

By /
Sql Distinct Clause

MySQL 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;Code language: SQL (Structured Query Language) (sql)

Examples of the DISTINCT Clause

Let us consider the following ConferenceGuests table.

Conferenceguests Table
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;Code language: SQL (Structured Query Language) (sql)

The output will be the following:

Mysql Distinct Using Single Column

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;Code language: SQL (Structured Query Language) (sql)

The output will be:

Mysql Distinct Using Multiple Columns

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

The MySQL 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;Code language: SQL (Structured Query Language) (sql)

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:

Mysql Count Distinct

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 count() function.

SELECT DISTINCT Country, Count(*) FROM ConferenceGuests GROUP BY Country;Code language: SQL (Structured Query Language) (sql)

You will get the following output:

Mysql Distinct Values With Count

Conclusion

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.

References