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;