How to Select Unique Values in MySQL

In this tutorial, we aim at understanding how to find unique values in a MySQL database.

MySQL provides us with a helpful statement to find the total number of distinct or different values from a particular column of a specified table. This statement is the SELECT DISTINCT.

This is generally used by businesses and enterprises to find the list or count of different users or customers in their database. Let us see this method in action.

However, first, let us create a dummy dataset.

-- create the table student_details
CREATE TABLE student_details(
	stu_id int,
	stu_firstName varchar(255) DEFAULT NULL,
	stu_lastName varchar(255) DEFAULT NULL,
	primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Preet","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Use SELECT DISTINCT to Select Unique Values in MySQL

The SELECT DISTINCT statement in MySQL helps us fetch unique values in a particular table. The basic syntax of the SELECT DISTINCT statement is as follows.

SELECT DISTINCT column_name
FROM name_of_the_table;

The aforementioned code returns the distinct entries from the column column_name that may or may not have duplicate entries.

Let us fetch all the students from the student_details table with distinct first names. The column associated with the first name values of students is stu_firstName.

Let us use SELECT DISTINCT to get this task done. We can utilize the following query to get the different or unique first name values from the student_details table.

SELECT DISTINCT stu_firstName
FROM student_details;

The output of the aforementioned query is as follows.

stu_firstName
Preet
Rich
Veron
Hash
Sachin
David

As seen in the above code block, we have only six unique first names out of the seven rows added in our student_details table. Thus, the output only has six names since Preet is repeated twice in our column stu_firstName.

We can use the following syntax if we only wish to find the total number of unique values.

SELECT COUNT(DISTINCT(column_name))
from name_of_the_table

In our case, we can use the following query to find the number of unique first names of the students from the stu_firstName column.

SELECT COUNT(DISTINCT(stu_firstName)) as total_unique_first_names
from student_details

The output of the aforementioned code block is as follows.

total_unique_first_names
6

Thus, with the help of the SELECT DISTINCT statement, we can efficiently find the total number of different entries or fetch all the unique values of a particular column from a table in MySQL.