What is DISTINCT clause in MySQL?

The DISTINCT clause in MySQL fetches the unique records from the table.

It is used with the SELECT statement and removes the duplicate records from the record set.

How to use DISTINCT clause

The simple syntax for using the MySQL DISTINCT clause:

SELECT DISTINCT col1, col2…

FROM tbl_name

WHERE condition

GROUP BY col(s)

ORDER BY (col);

So, you may specify one or more columns in the DISTINCT clause to remove duplicate records. I will show you examples of using one or more columns below.

An example of DISTINCT with single column

To show how the DISTINCT clause works, we have an employee table containing information like employee name, age, salary, and joining date.

For the demo, I entered duplicate employee names while other information may vary.

The DISTINCT query with one column, I used the employee name column for returning the unique names as follows:

Query:

SELECT DISTINCT employee_name

FROM  tst_employees;

The result:

You can see that the first query result shows seven records including two records of the employee named Mike. The second query returns six records, omitting the second occurrence of the name Mike.

The example of using two columns

The example below shows using two columns in the SELECT-DISTINCT query.

I am using the employee_name and employee_age columns that exist in the employee’s table as follows:

The query:

SELECT DISTINCT employee_name, employee_age

FROM  tst_employees

Result-set

This time, although the name exists twice for the employee name ‘Mike’, however, the age is different for both.

That is why the DISTINCT query returned seven records as I also included the age column in this query along with the name.

Using COUNT function with the DISTINCT clause example

In this example, we will get the total number of employees in the employee table along with unique employee names by using the MySQL COUNT with the DISTINCT clause.

Two queries:

SELECT COUNT(DISTINCT employee_name) AS "Unique by Name"

FROM tst_employees;

And

SELECT COUNT(DISTINCT employee_name, employee_age) AS "Unique by Name/Age"

FROM tst_employees;