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.

Table of Contents

## 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

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;
```

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 `count()`

function.

```
SELECT DISTINCT Country, Count(*) FROM ConferenceGuests GROUP BY Country;
```

You will get the following output:

## 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

- JournalDev article on SQL Distinct.
- MySQL documentation for the DISTINCT clause.