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:

MySQL DISTINCT

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

MySQL DISTINCT two

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.

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;

MySQL DISTINCT COUNT

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!