MySQL - Distinct

In this tutorial, you will learn how to use MySQL DISTINCT operator with the SELECTstatement to eliminate duplicate rows in the result set.
 
When querying data from a table, you may get duplicate rows. In order to remove the duplicate rows, you use the DISTINCT operator in the SELECT statement. The syntax of using the DISTINCT operator is as follows:
 
SELECT DISTINCT columns
FROM table_name
WHERE where_conditions
 
Let’s take a look a simple example of using the DISTINCT operator to select the distinct last names of employees from the employees table.
 
First, we query the last names of employees from the employees table using the SELECT statementas follows:
 
SELECT lastname
FROM employees
ORDER BY lastname
 
Some employees has the same last name  Bondur, Firrelli, etc. To remove the duplicate last names, you use the DISTINCT operator in the SELECT clause as follows:
 
SELECT DISTINCT lastname
FROM employees
ORDER BY lastname
 
The duplicate last names are eliminated in the result set when we use the DISTINCT operator.
 
MySQL DISTINCT and NULL values
 
If a column has NULL values and you use the DISTINCT operator for that column, MySQL will keep one NULL value and eliminate the other because the DISTINCT operator treats all NULL values as the same value.
 
For example, in the customers table, we have many rows with state column has NULL values. When we use the DISTINCT operator to query states of customers, we will see distinct states plus a NULL value as the following query:
 
SELECT DISTINCT state
FROM customers
 
MySQL DISTINCT with multiple columns
 
You can use the DISTINCT operator with more than one column. The combination of all columns will be used to define the uniqueness of the row in the result set.
 
For example, to get the 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 operator, you will get duplicate combination state and city as follows:
 
SELECT state, city
FROM customers
WHERE state IS NOT NULL
ORDER BY state, city
 
DISTINCT vs. GROUP BY Clause
 
If you use the GROUP BY clause in the SELECT statement without using aggregate functions, theGROUP BY clause will behave like the DISTINCT operator. The following queries produce the same result set:
 
SELECT DISTINCT state
FROM customers;
 
SELECT state
FROM customers
GROUP BY state;
 
The difference between DISTINCT operator and GROUP BY clause is that the GROUP BY clause sorts the result set whereas the DISTINCT operator does not.
 
MySQL DISTINCT and COUNT aggregate function
 
The DISTINCT operator 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 the unique states of customers in the U.S., you use the following query:
 
SELECT COUNT(DISTINCT state)
FROM customers
WHERE country = 'USA';
 
In this tutorial, we have shown you various ways of using MySQL DISTINCT operator such as eliminating duplicate records and counting non-NULL values.