Introduction to MySQL DISTINCT
Most of the tables that we use in our MySQL database contain columns except primary key columns that have duplicate values entries in them. For example, multiple developer records entries can have the same value in the technology column as they may work for the same technology. Like 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 of different values of the technology column from the developers’ table. For this, we can make the 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 the usage of distinct keyword, its syntax, examples with NULL values, aggregate functions, more than one columns 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 that contains specified columns and records that 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 developers table using 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 developers table using the below SELECT query –
Code:
SELECT * FROM `developers`;
Output:
Now, let us retrieve the list of all the technologies that are 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 NULL values in the table are not ignored by the DISTINCT clause. 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 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 that 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 position and technology columns. Note that here position and technology togetherly should have unique values. We can observe from the developers table records that there are two records with names Payal and Heena having the same technology and same position i.e angular and developer. So, both these columns should be retrieved in only one record in the resultset of the following queries. While all other records represent distinct technology, position combination.
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 GROUP BY clause is used on a single column and retrieved the column on which the group by clause is applied, it retrieves all the distinct values. Now, let us group the developers table based on the position column that will give us all the assigned positions list. For this, we will have to use the GROUP BY statement in the following way in 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 distinct query resultset of the following query
Code:
SELECT DISTINCT(position) FROM developers
Output:
Conclusion
The DISTINCT clause is used to retrieve the unique of 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 NULL values are also considered by the DISTINCT clause. The DISTINCT clause is a special case of the GROUP BY clause where the groping criteria are the same as the retrieved column.
Recommended Articles
This is a guide to MySQL DISTINCT. Here we discuss an introduction to MySQL DISTINCT, how does it work with query examples. You can also go through our other related articles to learn more –
