It is inevitable that some columns in our database will have duplicate values. For example, if we had a table of names, we might have persons with the same first name or last name or both.
If we wanted to retrieve only unique values from our table we would need to use the MySQL DISTINCT keyword. By using this keyword in our query, we instruct the database engine to return only unique values.
This distinct keyword is used with the MySQL select query as the tutorial below explains.
The DISTINCT keyword is used as follows:
SELECT DISTINCT column_name FROM table_name;
We instruct the database engine to perform a SELECT on the table, but we further instruct it to return only unique column values from the table.
Taking an employees table as an example, it is no doubt that we may have more than one employee with the same first name or last name.
Suppose our "employees" table looked like this:
If we wanted to retrieve all the last names of our employees, if we used:
SELECT last_name FROM employees;
We would get duplicates as shown below:
This is because we instructed MySQL to return all rows in our table.
To remove duplicates and show only unique last names, what we want to use is:
SELECT DISTINCT last_name FROM employees;
This would yield:
Which is exactly what we wanted.
In some cases, depending on what we are trying to achieve, we only need to concern ourselves with unique entries in our database. For example, if we had the addresses of persons from different countries and we wanted to do a country count, we would have to get distinct countries. It would make no sense to retrieve all records with some countries occurring more than once.
The MySQL distinct keyword is very straightforward so please feel free to move on to another of our MySQL tutorials.