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. In this article we will show you, How to write MySQL Distinct statement using the Workbench, and Command Prompt.

MySQL Select Distinct Syntax

The basic syntax of the Select Distinct in MySQL can be written as:

• 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: One or more tables present in the Database. Use Joins to join multiple table.

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

SQL DISTINCT on Single Column

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

OUTPUT

Now, let me use the Distinct keyword

OUTPUT

MySQL Distinct on Multiple Columns

When we use MySQL Distinct on multiple columns then the SELECT Statement will write the unique combination of multiple columns instead of unique individual records. In this example, We are going to select the unique records present in the Education Column, and Profession Column.

OUTPUT

Although we used the 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 Example – Where Clause

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

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 below screenshot is showing 5 records.

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

MySQL Distinct Vs Group By

In MySQL, Distinct behavior is inherits from the Group By. If you use the Group By Clause without any Aggregate Function then it will act as the Distinct keyword. And the Only difference between them is:

• Group By will sort the Data first, and then perform grouping.
• Distinct keyword does not perform any sorting.

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

OUTPUT

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

As you can see they are returning same result but not in the same order

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

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

MySQL Distinct Example – Command Prompt

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

OUTPUT

Thank You for Visiting Our Blog