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.

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:

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:

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:

Let us retrieve the result using the select query –

SELECT * FROM `developers`;

Output:

Let us now check the output of distinct query –

Code:

SELECT DISTINCT(technology) AS List_Of_Technologies FROM developers;

Output:

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:

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:

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:

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