SQL SELECT DISTINCT

SQL SELECT DISTINCT: The aim of this tutorial is to teach you how to use the SQL SELECT DISTINCT clause along with syntax and examples.

The SQL SELECT DISTINCT clause is used to remove the duplicate records from the result set.

Sometimes, you may need to find only the unique values from a particular column(s). For example, suppose you have a product table which contains product-related information and you want to find out the unique product name across the table. In this situation, SQL Select distinct clause will come into play.

Let’s check the syntax and some examples which will help you to clear your concept in this topic.

Syntax

The basic syntax of the SQL DISTINCT clause is as below.

SELECT DISTINCT expressions
FROM tables
[WHERE Conditions];

In this syntax,

• expressions – the columns or calculations you want to fetch from the table(s).
• tables – Name of the tables from where you want to retrieve records. There should be at least one table after the FROM clause.
• WHERE Conditions – The condition should be met in order to be part of the resultset. There can be more than one condition in the WHERE clause.

Note

• In the case of only one column in the expressions, DISTINCT will return the unique value for that column only.
• If you are using multiple columns in the expressions, DISTINCT will return unique values combination of the columns list.
• NULL values present in the column also consider in the DISTINCT clause. So, NULL values will also be returned as part of the result set.

Example – Finding Unique Values from a column

In this example, we are going to show how you can find unique values from a column using the DISTINCT clause.

Let take a student table with the following data in it.

Now, let’s select HomeTown from the above table for all the students.

mysql> select hometown from students;
+----------+
| hometown |
+----------+
| Durgapur |
| Siliguri |
| Durgapur |
| Malda |
+----------+
4 rows in set (0.001 sec)

You can see from the above result that duplicate HomeTown name appears in the resultset.

Now you can easily eliminate duplicate values using distinct statement.

mysql> select distinct hometown from students;
+----------+
| hometown |
+----------+
| Durgapur |
| Siliguri |
| Malda |
+----------+
3 rows in set (0.001 sec)