SQL Server SELECT DISTINCT

Learning Objective

The objective of this tutorial is to teach you how to use the DISTINCT clause in SQL Sarver SELECT query. It removes duplicates and returns only different or unique values from a column in the output.

Introduction to SQL Server Select Distinct Clause

The SQL Server SELECT DISTINCT clause is used to remove duplicate records from the resultset. The DISTINCT clause can operate on one or more columns and when applied returns only distinct (or unique) values from the specified columns.

SQL Server SELECT DISTINCT Syntax

The basic syntax of SQL Server Select DISTINCT as follow.

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

In this syntax,

• expressions – The columns or calculations that you wish to retrieve.
• tables – Name of the tables from where data will be retrieve.
• [WHERE conditions] – Optional. The condition must be met to be part of the result-set.

SQL Server SELECT DISTINCT Examples

Let’s take a table of employee with the following data.

emp_id	emp_name	emp_phone	emp_gender	dept_code	emp_location
1270	Kalyan Purkayastha	9620139678	M	119	Kolkata
1271	Rajesh Pandey	9611895588	M	109	Bangalore
1272	Annie Bora	8884692570	F	121	Bangalore
1273	Dipankar Karmakar	9957889640	M	119	Kolkata
1274	Sunitha Rai	9742067708	F	109	Mumbai
1276	Parag Barman	8254066054	M	121	Kolkata
1277	Vinitha Sharma	9435746645	F	121	Mumbai
1278	Abhishek Saha	9850157207	M	109	Kolkata
1279	Rushang Desai	9850157207	M	109	Mumbai
1280	Arvin Kumar	8892340054	M	119	Bangalore

Now let’s check different usages of SQL Server Select DISTINCT clause.

1) DISTINCT one column example

The following statement will returns all the emp_location of employees.

SELECT
   emp_location 
FROM employees;

emp_location

Kolkata

Bangalore

Bangalore

Kolkata

Mumbai

Kolkata

Mumbai

Kolkata

Mumbai

Bangalore

You can see that the resultset contains duplicate records. To get only unique records use DISTINCT clause as follows.

SELECT DISTINCT 
   emp_location 
FROM employees;

2) DISTINCT Multiple Columns example

The following statement will return all the rows of columns dept_code and emp_location.

SELECT
  dept_code,
  emp_location
FROM 
  employees;

dept_code	emp_location
119	Kolkata
109	Bangalore
121	Bangalore
119	Kolkata
109	Mumbai
121	Kolkata
121	Mumbai
109	Kolkata
109	Mumbai
119	Bangalore

And the below statement will returns unique dept_code and emp_location.

SELECT DISTINCT
  dept_code,
  emp_location
FROM 
  employees;

3) DISTINCT Value Count

DISTINCT can also be used to get a count of the distinct values in a column.

Example of the same using the sample table. From this query we can get to know the number of locations where the company operates or from where employees work.

SELECT
  COUNT (DISTINCT emp_location) AS 'Employee Location Count'
FROM 
  employees;

4) DISTINCT with NULL value example

Now we will insert another two rows in our existing employees table as below.

insert into employees values (1281,'Sagar Sharma',NULL,'M',119,'Mumbai');
insert into employees values (1282,'Anuj Saxena',NULL,'M',121,'Bangalore');

Now data in the table employees will be as below. The following query will find the distinct phone no of employees.

select distinct
  emp_phone
from
  employees;

In the above example, DISTINCT returns only one NULL values out of two. So it is clear that DISTINCT will return only one NULL value irrespective of the number of NULL values in the column.