- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
DISTINCT Clause in MS SQL Server
A DISTINCT clause is used to remove duplicate entries from the table. This clause can only be used with a select statement. Sometimes, a column contains some duplicate values, so to remove it, we use the DISTINCT clause as it will return only different values present in the column. When one or more than one expression is provided, the DISTINCT clause will return a unique or a combination of unique values. In case of null, the DISTINCT clause will not ignore it and return null as a value.
SELECT DISTINCT expressions FROM table [WHERE conditions];
Expressions are columns from which data is extracted.
The table is the table name on which the operation is to be performed.
Conditions are optional and used to select specific data.
Condition 1:When no null value is present
CREATE TABLE Customer(ID INT PRIMARY KEY, Name VARCHAR(50), Country VARCHAR(50), Age int(2)); #Table created INSERT INTO Customer (ID, Name, Country, Age) VALUES (1, 'Monu', 'India', 23), (2, 'Aman ', 'Australia', 25), (3, 'Naman', 'Sri lanka', 24), (4, 'Aditya', 'Australia', 26), (5, 'Raman , 'Spain', 22); #data inserted Select * from Customer;#To show table
ID Name Country Age 1 Monu India 23 2 Aman Australia 25 3 Naman Sri Lanka 24 4 Aditya Australia 26 5 Raman Spain 22
To remove Duplicate countries.
SELECT DISTINCT Country FROM Customer;
Country India Australia Sri Lanka Spain
There is a duplicate (Australia) present in the table which is removed here.
When no duplicates in present
SELECT DISTINCT name FROM Customer;
Name Monu Aman Naman Aditya Raman
Here, no duplicate was present in the name column, so the same rows are returned.
Condition 2:When a null value is present
CREATE TABLE Customer(ID INT PRIMARY KEY, Name VARCHAR(50), Country VARCHAR(50), Age int(2)); #Table created INSERT INTO Customer (ID, Name, Country, Age) VALUES (1, 'Monu', 'India', 23), (2, 'Aman ', 'Australia', null), (3, 'Naman', 'null', 24), (4, 'Aditya', 'null', null) #data inserted Select * from Customer;#To show table
ID Name Country Age 1 Monu India 23 2 Aman Australia 3 Naman 24 4 Aditya
Example: Distinct clause when null values are present
SELECT DISTINCT Name, Country, Age FROM Customer;
Name Country Age Monu India 23 Aman Australia Naman 24 Aditya
Condition 3: Distinct vs group by
CREATE TABLE customer ( id INT, name VARCHAR(50), age INT, country VARCHAR(50), email id VARCHAR(100) ); INSERT INTO customer (id, name, age, country, email id) VALUES (1, 'monu', 23, 'india', 'monu@gmail. com'), (2, 'aman', 20, NULL, 'aman@gmail. com'), (3, 'monu', 23, 'canada', NULL), (4, 'raman', NULL, NULL, NULL);
id name age city email id 1 monu 23 India email@example.com 2 aman 20 firstname.lastname@example.org 3 monu 23 Canada 4 raman
In the case of distinct
SELECT DISTINCT name, city FROM customer;
name city monu India aman monu Canada raman
In the case of groups by
SELECT name, age FROM customer GROUP BY name, age;
name age monu 23 aman 20 raman
This article consists of a distinct clause, which is used to remove duplicate data from the table. three conditions are taken to explain it. First is when no null values are present, the duplicate is removed when there is any duplicate value, otherwise the same is returned. Second is when a null value is present, the Distinct clause removes other null values and only one is shown. Third is a group by vs distinct where both are almost the same other than query syntax.
Kickstart Your Career
Get certified by completing the courseGet Started