What is DISTINCT clause in MySQL?

The DISTINCT clause in MySQL is used to fetch the unique records from the table. The DISTINCT is used with the SELECT statement and removes the duplicate records from the recordset.

How to use DISTINCT clause

The simple syntax for using the MySQL DISTINCT clause is:

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 for removing duplicate records. I will show you examples of using one or more columns below.

An example of DISTINCT with single column

For showing how DISTINCT clause works, we have an employee table that contains information like employee name, age, salary and joining date. For the demo, I entered duplicate employee names while other information may vary.

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

Query:

SELECT DISTINCT employee_name

FROM  tst_employees;

The result:

You can see, the first query result shows seven records that include two records of the employee names Mike. The second query returns six records, omitting the second occurrence of 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 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 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.

Note: The COUNT function returns the total count of rows for the specified column(s). You may use it with the simple column as well as the aggregated data.

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;