Summary: in this tutorial, you will learn how to use MySQL DISTINCT operatorwith the SELECT statement 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 statement 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 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 NULLvalue 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, 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 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.
随时随地看视频
