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.

Syntax

SELECT DISTINCT expressions
FROM table
[WHERE conditions];

Here,

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

Example

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

Output

Customer

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

Example 1

To remove Duplicate countries.

SELECT DISTINCT Country FROM Customer;

Output

Country
India 
Australia
Sri Lanka
Spain

There is a duplicate (Australia) present in the table which is removed here.

Example 2

When no duplicates in present

SELECT DISTINCT name FROM Customer;

Output

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

Example

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

Output

Customer

ID 	Name	 Country 	  Age
1	Monu	 India	          23
2	Aman	 Australia	
3	Naman		          24
4	Aditya		

Example: Distinct clause when null values are present

Example

SELECT DISTINCT Name, Country, Age FROM Customer;

Output

Name	Country 	Age
Monu	India	        23
Aman	Australia	
Naman		        24
Aditya		

Condition 3: Distinct vs group by

Example

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);

Output

id	name	age	 city	     email id
1	monu	23	 India	     monu@gmail.com
2	aman	20		     aman@gmail.com
3	monu	23	 Canada	
4	raman			

In the case of distinct

Example

SELECT DISTINCT name, city FROM customer;

Output

name	  city
monu	  India
aman	
monu	  Canada
raman	

In the case of groups by

Example

SELECT name, age FROM customer GROUP BY name, age;

Output

name	 age
monu	 23
aman	 20
raman	

Conclusion

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.