Distinct clause in MS SQL Server
In this article, we will be discussing about distinct clause in MS SQL Server.
Introduction :
- A table has a maximum of 1000 rows constituted.
- The probability of repeated rows otherwise called as duplicates in SQL terms might occur in the table.
- 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.
Please Login to comment...