MySQL DISTINCT
The DISTINCT
keyword can be used within an SQL statement to remove duplicate rows from the result set of a query.
Consider the following example (which doesn't use the DISTINCT
option):
Result:
![Screenshot of query without the DISTINCT option](/pix/mysql/examples/mysql_distinct_1.png)
You can see that there are two records containing the value of Angela
.
Now let's add the DISTINCT
keyword:
Result:
![Screenshot of query with the DISTINCT keyword](/pix/mysql/examples/mysql_distinct_2.png)
There is now only one record that contains the value of Angela
. This is because the DISTINCT
keyword removed the duplicates. Therefore, we know that each row returned by our query will be distinct — it will contain a unique value.
Using DISTINCT
with COUNT()
You can insert the DISTINCT
keyword within the COUNT()
aggregate function to provide a count of the number of matching rows.
Like this:
Result:
![Screenshot of query and result](/pix/mysql/examples/mysql_distinct_3.png)
If we remove the DISTINCT
option (but leave COUNT()
in):
We end up with 4
(instead of 3
as we did when using DISTINCT
):
![Screenshot of query and result](/pix/mysql/examples/mysql_distinct_4.png)
Multiple Columns
You can use DISTINCT
with multiple columns. If you do select more than one column, all columns are combined to create the uniqueness of the row. This is because the DISTINCT
option looks for a distinct row, rather than a distinct column.
Result:
![Screenshot of query and result](/pix/mysql/examples/mysql_distinct_5.png)
The above query returns 4 rows instead of 3, because, even though there are still two Angelas, they are now unique due to their last name being different. If they both shared the same last name, only 3 records would have been returned.
DISTINCTROW
There is also a DISTINCTROW
keyword which is a synonym for DISTINCT
. So you can use one or the other.
So this:
…could also be written as this:
The examples on this page use the Sakila sample database.