The MySQL Distinct Statement is used to retrieve unique rows (by removing the duplicate rows) from the specified column in the SELECT Statement.
MySQL Select Distinct Syntax
The basic syntax of Select Distinct in MySQL can write as shown below.
SELECT DISTINCT [Columns] FROM Source WHERE Conditions -- This is Optional
- DISTINCT: This keyword returns Unique Columns
- Columns: It allows us to choose the number of columns we want to use from the table. It may be one or more.
- Source: It can be one or more tables in a Database. Use Joins to combine multiple tables.
We are going to use the below shown data to explain the MySQL Select Distinct with an example.
MySQL Select Distinct on Single Column
In this MySQL Distinct Single column example, We are going to select the unique records from the Education Column. Before we get unique records, let me select all the records from the education column.
USE company; SELECT Education FROM customerdetails;
Now, let me use this keyword in the SELECT Statement.
USE company; SELECT DISTINCT Education FROM customerdetails;
MySQL Select Distinct on Multiple Columns
When we use MySQL Distinct on multiple columns, then the SELECT Statement writes the unique combination of multiple columns instead of unique individual records.
In this MySQL example, We are going to select the unique records present in the Education Column and Profession Column.
USE company; SELECT DISTINCT Education, Profession FROM customerdetails ORDER BY Education, Profession;
Although we used the Distinct Keyword in the SELECT Statement. From the above screenshot, you can see that it is returning duplicates because
- Bachelors and Developer is a unique Combination.
- Bachelors and Programming is a unique Combination, etc.
Distinct Where Clause Example
In this example, we show you, How to use this statement along with the WHERE Clause. The following statement returns the unique Education, Profession from the customers table whose Yearly Income is greater than or equal to 85000.
USE company; SELECT DISTINCT Education, Profession FROM customerdetails WHERE Yearly_Income > 85000;
Although there are 13 unique records for the Education, professional Column combination. 10 records are not matching the condition in the Where clause. That’s why the screenshot below shows 5 records.
NOTE: It considers the NULL records as valid unique records. So, Please use any Not Null function to remove NULLS.
MySQL Distinct Vs Group By
The Select Distinct behavior is inherited from the Group By. If you use the Group By Clause without any Aggregate Function, then it acts as the Distinct keyword. And the only difference between them is:
- Group By sorting the Data first, and then performing grouping.
- This keyword does not perform any sorting.
USE company; SELECT DISTINCT Profession FROM customerdetails;
Let me remove the keyword and use the Group By clause:
USE company; SELECT Profession FROM customerdetails GROUP BY Profession;
As you can see, they are returning the same result but not in the same order
This time, I used this Keyword along with the Order By
USE company; SELECT DISTINCT Profession FROM customerdetails ORDER BY Profession ASC;
As you can see, it is returning the same result as Group By
Select Unique Rows With Order By Clause
Let me show you how to select distinct records using the MySQL Order By clause command prompt. Here we are selecting the distinct Education and profession records from the customer details column.
USE company; SELECT DISTINCT Education, Profession FROM customerdetails ORDER BY Education ASC, Profession ASC;