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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.