Summary: in this tutorial, you will learn how to use MySQL DISTINCT with the SELECT statement to eliminate duplicate records in the result set.

When querying data from a database table, you  may get duplicate records. In order to remove duplicate records, you use the DISTINCT keyword along with the SELECT statement. The syntax of the MySQL DISTINCT is as follows:

1
2
3
SELECT DISTINCT columns
FROM table_name
WHERE where_conditions

Let’s take a look a simple example of using DISTINCT to select unique last name of employees from the employees table.

First, we query the last names of employees from the employees table using the SELECT statement as follows:

1
2
3
SELECT lastname
FROM employees
ORDER BY lastname

mysql-distinct-query_1

We have employee records that have the same last name so  we get duplicate last names in the result set. To remove the duplicate last names, you use the DISTINCT after the SELECT clause as follows:

1
2
3
SELECT DISTINCT lastname
FROM employees
ORDER BY lastname

mysql-distinct-last-name
You can see in the new result set, the duplicate records are eliminated.

MySQL DISTINCT and NULL values

If the column, on which we are applying DISTINCT, has NULL values, MySQL DISTINCT will keep one NULL value and eliminate other because MySQL DISTINCT treats all NULL values of a column as the same value.

For example, in the customer table, we have many records that has state with NULL values. When we use DISTINCT to query state of customer, we will see unique state plus NULL state. See the query below:

SELECT DISTINCT state
FROM customers

mysql-distinct-null-value

MySQL DISTINCT with multiple columns

You can use the DISTINCT keyword with more than one column. The combination of all columns will be used to define the uniqueness of the record in the result set. For example, to get the unique combination of city and state from the customers table, you use the following query:

1
2
3
4
SELECT DISTINCT state, city
FROM customers
WHERE state IS NOT NULL
ORDER BY state, city

The output of the query is as follows:

mysql-distinct-multiple-columns

Without the DISTINCT keyword, you will get duplicate combination state and city as below:

1
2
3
4
SELECT state, city
FROM customers
WHERE state IS NOT NULL
ORDER BY state, city

mysql-distinct-multiple-columns-duplicate

DISTINCT vs. GROUP BY Clause

If you use GROUP BY clause in the SELECT statement without using aggregate functions, the GROUP BY clause will work like the DISTINCT. The following queries produce the same result set:

1
2
3
4
5
6
SELECT DISTINCT state
FROM customers;
SELECT state
FROM customers
GROUP BY state;

The difference between DISTINCT and GROUP BY is that the GROUP BY clause sort the result set while DISTINCT does not.

MySQL DISTINCT and COUNT aggregate function

The MySQL DISTINCT  is used with the COUNT function to count unique records in a table. In this case, it ignores the NULL values. For example, to count unique states of our customers in the US, you use the following query:

1
2
3
SELECT COUNT(DISTINCT state)
FROM customers
WHERE country = 'USA';

mysql-distinct-count

In this tutorial, we have shown you various ways of using MySQL DISTINCT such as eliminating duplicate records and counting non-NULL values.

From http://www.mysqltutorial.org/