MySQL DISTINCT

 MySQL DISTINCT

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

Introduction to MySQL DISTINCT clause

When querying data from a table, you may get duplicate rows. In order to remove these duplicate rows, you use the DISTINCT clause in the SELECT statement.

Here is the syntax of the DISTINCT clause:

SELECT DISTINCT
    select_list
FROM
    table_name;

MySQL DISTINCT examples

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

First, query the last names of employees from the employees table using the following SELECT statement:

SELECT 
    lastname
FROM
    employees
ORDER BY 
    lastname;

As clearly shown in the output, some employees have the same last name e.g,  Bondur,Firrelli .

This statement uses the DISTINCT clause to select unique last names from the employees table:

SELECT 
    DISTINCT lastname
FROM
    employees
ORDER BY 
    lastname;

As you can see from the output, duplicate last names have been eliminated in the result set.

MySQL DISTINCT and NULL values

If a column has NULL values and you use the DISTINCT clause for that column, MySQL keeps only one NULL value because DISTINCT treats all NULL values as the same value.

For example, in the customers table, we have many rows whose state column has NULL values.

When you use the DISTINCT clause to query the customers’ states, you will see unique states and  NULL as the following query:

SELECT DISTINCT state
FROM customers;

MySQL DISTINCT with multiple columns

You can use the DISTINCT the clause with more than one column. In this case, MySQL uses the combination of values in these columns to determine the uniqueness of the row in the result set.

For example, to get a unique combination of city and state from the customers table, you use the following query:

SELECT DISTINCT
    state, city
FROM
    customers
WHERE
    state IS NOT NULL
ORDER BY 
    state, 
    city;

Without the DISTINCT clause, you will get the duplicate combination of state and city as follows:

SELECT 
    state, city
FROM
    customers
WHERE
    state IS NOT NULL
ORDER BY 
    state , 
    city;

 DISTINCT clause vs. GROUP BY clause

If you use the GROUP BY clause in the SELECT statement without using aggregate functions, the GROUP BY clause behaves like the DISTINCT clause.

The following statement uses the GROUP BY clause to select the unique states of customers from the customers table.

SELECT 
    state
FROM
    customers
GROUP BY state;

You can achieve a similar result by using the DISTINCT clause:

SELECT DISTINCT
    state
FROM
    customers;

Generally speaking, the DISTINCT a clause is a special case of the GROUP BY clause. The difference between DISTINCT clause and GROUP BY a clause is that the GROUP BY clause sorts the result set whereas the DISTINCT the clause does not.

Notice that MySQL 8.0 removed the implicit sorting for the GROUP BY clause. Therefore, if you use MySQL 8.0+, you will find that the result set of the above query with the GROUP BY clause is not sorted.

If you add the ORDER BY clause to the statement that uses the  DISTINCT clause, the result set is sorted and it is the same as the one returned by the statement that uses GROUP BY clause.

SELECT DISTINCT
    state
FROM
    customers
ORDER BY 
    state;

MySQL DISTINCT and aggregate functions

You can use the DISTINCT the clause with an aggregate function e.g., SUM, AVG, and COUNT, to remove duplicate rows before the aggregate functions are applied to the result set.

For example, to count the unique states of customers in the U.S., you use the following query:

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

MySQL DISTINCT with LIMIT clause

In case you use the DISTINCT clause with the LIMIT clause, MySQL immediately stops searching when it finds the number of unique rows specified in the LIMIT clause.

The following query selects the first five non-null unique states in the customers table.

SELECT DISTINCT
    state
FROM
    customers
WHERE
    state IS NOT NULL
LIMIT 5;

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