Distinct clause in MS SQL Server

In this article, we will be discussing about distinct clause in MS SQL Server.

Introduction :

1. A table has a maximum of 1000 rows constituted.
2. The probability of repeated rows otherwise called as duplicates in SQL terms might occur in the table.
3. In SQL Server, distinct is a term used to remove duplicates from a table.

Basic syntax :

select distinct
select_list
from
table_name 

Example :

Sample table – Student

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE

To remove the duplicates, the query must be written as follows –

select distinct
roll number, name, course
from student 

The output will be as follows –

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE

As there are no duplicates, same number of rows are returned. Let us see an example having duplicates.

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE
111	Riya	CSE

The query should be written as –

select distinct
roll number, name, course
from student 

Output is –

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Deepa	EEE

There was a duplicate named Riya but by using distinct, the duplicates are removed.

Consider another example of null values.

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	–	–

Note that the name and the course are null in case of Deepa, so the query is written as –

select distinct 
name, roll number, course 
from student 

The output is as follows –

Roll number	Name	Course
111	Riya	CSE
112	Apoorva	ECE
113	Mina	Mech
114	Rita	Biotechnology
115	Veena	Chemical
116	Null

In case of null values, distinct removes all the other null values and restores only one null value as shown in the output.