EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DISTINCT

MySQL DISTINCT

By Payal UdhaniPayal Udhani

MySQL-DISTINCT

Introduction to MySQL DISTINCT

Most of the tables we use in our MySQL database contain columns except primary key columns with duplicate values entries. For example, multiple developer record entries in the technology column can have the same value as they may work for the same technology. Some developers may be using angular. Some may work with Java, some with react js, etc. To get the list of the technologies used by the developers, we will have to retrieve the unique values of the technology column from the developers’ table. For this, we can make use of the distinct keyword. In MySQL, the distinct keyword or clause helps us to retrieve the unique or different values of the column in the table.

In this article, we will learn about using distinct keywords, their syntax, examples with NULL values, aggregate functions, more than one column with a distinct function, and the similarities between distinct and group by clause.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

SELECT DISTINCT
list_of_columns
FROM
name_of_table;

Where,

list_of_columns is the list of the names of columns or a single column on which you want to apply the distinct functionality, and the name_of_table is the table name containing specified columns and records you want to retrieve.

Example to Implement MySQL DISTINCT

Let us consider one example, Create one table names developers using the following create a query

Code:

CREATE TABLE `developers` (
`developer_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
`technology` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`developer_id`,`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Let us insert some records in the developers’ table using the insert statement

Code:

INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('1','1','Payal','Developer','Angular','30000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('2','1','Heena','Developer','Angular','10000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('3','3','Vishnu','Manager','Maven','25000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('4','3','Rahul','Support','Digital Marketing','15000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('5','3','Siddhesh','Tester','Maven','20000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('6','7','Siddharth','Manager','Java','25000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('7','4','Brahma','Developer','Digital Marketing','30000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('8','1','Arjun','Tester','Angular','19000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('9','2','Nitin','Developer','MySQL','20000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('10','2','Ramesh','Administrator','MySQL','30000');

We have inserted multiple values in my table using the same insert format. Now, after retrieving the records of the developers’ table using the below SELECT query –

Code:

SELECT * FROM `developers`;

Output:

SELECT

Now, let us retrieve the list of all the technologies used in the developers’ table using the distinct clause on the technology column. The query statement for this will be as follows –

Code:

SELECT DISTINCT(technology) AS List_Of_Technologies FROM developers;

Output:

SELECT technology

As we can see, all the duplicate values of technology value are skipped, and only distinct and different values are added in the result set.

DISTINCT clause with NULL values

The DISTINCT clause does not ignore the NULL values in the table. If a column on which the distinct clause is applied contains the value NULL in more than one record, then all the NULL values are retrieved and represented by a single NULL value in the DISTINCT resultset. Let us insert two records in the developer’s table with NULL technology and see the results of the DISTINCT clause in the query.

Code:

INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('11','2','Rohan','Admin',NULL,'20000');
INSERT INTO `developers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES('12','2','Raj','Designer',NULL,'30000');

Output:

NULL values

Let us retrieve the result using the select query –

SELECT * FROM `developers`;

Output:

MySQL DISTINCT - 4

Let us now check the output of distinct query –

Code:

SELECT DISTINCT(technology) AS List_Of_Technologies FROM developers;

Output:

MySQL DISTINCT - 5

DISTINCT clause on more than one column

We can use the DISTINCT clause on one or more columns. When defined on one column, it retrieves the unique values of that column. But when the DISTINCT clause is defined on more than one column, then the combination of the values of the columns is considered for uniqueness. Let us clarify our concept with the help of an example.

Let us apply the DISTINCT clause on the position and technology columns. Note that here position and technology togetherly should have unique values. We can observe from the developer’s table records that there are two records with names Payal and Heena having the same technology and position, i.e., angular and developer. So, both these columns should be retrieved in only one record in the resultset of the following queries. At the same time, all other records represent distinct technology and position combinations.

Code:

SELECT DISTINCT position, technology FROM developers;

Output:

one column

That contains 11 records, while the table contained 12 records as the two records named Heena and Payal had the same technology and position values of combination.

Retrieving DISTINCT results using simple GROUP BY clause

DISTINCT is one of the special cases of the GROUP BY clause. When the GROUP BY clause is used on a single column and retrieved from the column on which the group by clause is applied, it retrieves all the distinct values. Let us group the developer’s table based on the position column that will give us the list of assigned positions. For this, we will have to use the GROUP BY statement in the following way in the SELECT query –

Code:

SELECT position FROM developers GROUP BY position;

Output:

MySQL DISTINCT - 7

This gives the list of all the distinct positions in the developer’s table. Let us cross-check our result by comparing it with the output of the distinct query resultset of the following query.

Code:

SELECT DISTINCT(position) FROM developers

Output:

MySQL DISTINCT - 8

Conclusion – MySQL DISTINCT

The DISTINCT clause is used to retrieve the unique or different values of the columns of the table that contain duplicate values in their column. This clause can be used on single or multiple columns. When used with multiple columns, the combined value of all those columns collectively is considered for uniqueness. The DISTINCT clause also considers the NULL values. The DISTINCT clause is a particular case of the GROUP BY clause where the groping criteria are the same as the retrieved column.

Recommended Articles

We hope that this EDUCBA information on “MySQL DISTINCT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Subquery
  2. Features of MYSQL admin Tool
  3. MYSQL Administration
  4. IF Statement in MySQL
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more