MySQL Distinct

The MySQL Select 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 the Select Distinct in MySQL can write as:

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 you want to use from the table. It may be one or more.
  • Source: It can be one or more tables in a Database. Use MySQL Joins to join multiple tables.

We are going to use the below shown data to explain the Select Distinct in MySQL with example.

MySQL Distinct 1

SQL DISTINCT on Single Column

In this MySQL Distinct Single column example, We are going to select the unique records from the Education Column using Select Distinct. Before we get unique records, let me select all the records from the education column.

-- MySQL SELECT DISTINCT Example
USE company;
SELECT Education
FROM customerdetails;
MySQL Distinct 2

Now, let me use the Distinct keyword in the SELECT Statement.

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Education
FROM customerdetails;
MySQL Distinct 3

MySQL 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.

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Education, Profession
FROM customerdetails
ORDER BY Education, Profession;
MySQL Distinct 4

Although we used the MySQL Distinct Keyword in the SELECT Statement. From the above screenshot you can see, it is returning duplicates because

  • Bachelors and Developer is a unique Combination
  • Bachelors and Programming is a unique Combination, etc.

MySQL Distinct Where Clause Example

In this example, we show you, How to use the MySQL Select Distinct Statement along with WHERE Clause. The following statement returns the Distinct Education, Profession from customers table whose Yearly Income is greater than or equal to 85000.

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Education, Profession
FROM customerdetails
WHERE Yearly_Income > 85000;
MySQL Distinct 5

Although there are 13 distinct records for the Education, professional Column combination. 10 records are not matching the condition in Where clause. That’s why the screenshot below is showing 5 records.

NOTE: The Select Distinct considers the NULL records as a valid unique record. So, Please use any Not Null function to remove NULLS.

MySQL Distinct Vs Group By

The MySQL Select Distinct behavior 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 sort the Data first, and then perform grouping.
  • The distinct keyword does not perform any sorting.

If you use the Distinct Keyword along with the Order By then, it provides the same result as Group By. The following statement returns the Distinct Profession values from customer details

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Profession 
FROM customerdetails;
MySQL Distinct 6

Let me remove the distinct keyword, and use the Group By clause:

-- MySQL SELECT DISTINCT Example
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

MySQL Distinct 7

This time, I use Distinct Keyword along with the Order By

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Profession 
FROM customerdetails
ORDER BY Profession ASC;

As you can see, it is returning same result as Group By

MySQL Distinct 8

MySQL Select Distinct Command Prompt Example

Let me show you how to select unique records using the command prompt. Here we are selecting the distinct Education, and profession records from customer details column using select distinct

-- MySQL SELECT DISTINCT Example
USE company;
SELECT DISTINCT Education, Profession 
FROM customerdetails
ORDER BY Education ASC, Profession ASC;
MySQL Distinct 9