Summary: in this tutorial, you will learn how to use MySQL DISTINCToperatorwith 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 DISTINCToperator in the SELECT statement. The syntax of using the DISTINCToperator 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 employeestable using the SELECTstatement as 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 DISTINCToperator in the SELECTclause 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 NULLvalues and you use the DISTINCToperator for that column, MySQL will keep one NULLvalue and eliminate the other because the DISTINCToperator treats all NULLvalues as the same value.
For example, in the customerstable, we have many rows with state column has NULLvalues. When we use the DISTINCToperator 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 DISTINCToperator 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 customerstable, you use the following query:SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city
Without the DISTINCToperator, 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 SELECTstatement without using aggregate functions, the GROUP 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 DISTINCToperator and GROUP BY clause is that the GROUP BY clause sorts the result set whereas the DISTINCToperator 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 NULLvalues. 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 DISTINCToperator such as eliminating duplicate records and counting non-NULL values.
Related Tutorials