Skip to content
Related Articles

Related Articles

Distinct clause in MS SQL Server
  • Last Updated : 10 Jun, 2020

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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE

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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE

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



Roll numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE
111RiyaCSE

The query should be written as –

select distinct
roll number, name, course
from student 

Output is –

Roll numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE

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

Consider another example of null values.

Roll numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116Null


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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :