Hence, to get a list of unique values, such as the various types of movie ratings, we need to get only the unique values using the MySQL distinct keyword.
This tutorial will go over how to use the distinct keyword in MySQL queries to get unique results.
Before we begin, we assume you have MySQL installed on your system and can perform operations on the databases.
If you need a sample database to work with, consider the Sakila database in the resource provided below:
The general syntax for the MySQL DISTINCT clause is:
Here, the column_list is the set of columns that you wish to retrieve separated by commas. The table name is the table from which to select the said values.
Example Use Case
Let us now illustrate how to use MySQL DISTINCT using an example. We are using the Sakila sample database for illustration.
In the Sakila database, you will find the actors’ table, which contains fields as shown in the command below:
The output describing the table fields are shown below:
| Field | Type | Null | Key |
| actor_id | smallint unsigned | NO | PRI |
| first_name | varchar(45) | NO | |
| last_name | varchar(45) | NO | MUL |
| last_update | timestamp | NO | |
NOTE: I’ve truncated this table to show only relevant information.
If we select the values in the actors’ table and order by the first name, the chances are high that we will have duplicate values, as shown in the query below:
We can see from the output that there are duplicate first names as shown below:
NOTE: We limit the output to the 10 first values as the table contains massive information. Feel free to remove the limit and see how many values there are.
| first_name | last_name |
| ADAM | HOPPER |
| ADAM | GRANT |
| AL | GARLAND |
| ALAN | DREYFUSS |
| ALBERT | NOLTE |
| ALBERT | JOHANSSON |
| ALEC | WAYNE |
| ANGELA | WITHERSPOON |
| ANGELA | HUDSON |
| ANGELINA | ASTAIRE |
10 rows in set (0.00 sec)
Using the DISTINCT clause in MySQL, we can filter to get unique first names from the same table as shown in the query below:
Once we execute the query above, we will get a list of unique first names.
Example Use Case: Aggregate Functions
You can also use DISTINCT inside a MySQL aggregate function such as COUNT and SUM. For example, to use along with COUNT from the above query, we can do:
The above query gives us the number of names of the distinct first names where the last_name is BERRY.
NOTE: It is good to keep in mind that even NULL values are considered duplicates by the DISTINCT clause. Hence, if you have multiple null values, only one will be returned.
As seen in this tutorial, you can use the MySQL DISTINCT clause to fetch unique values from a table field that contains duplicate values.