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_idemp_nameemp_phoneemp_genderdept_codeemp_location
1270Kalyan Purkayastha9620139678M119Kolkata
1271Rajesh Pandey9611895588M109Bangalore
1272Annie Bora8884692570F121Bangalore
1273Dipankar Karmakar9957889640M119Kolkata
1274Sunitha Rai9742067708F109Mumbai
1276Parag Barman8254066054M121Kolkata
1277Vinitha Sharma9435746645F121Mumbai
1278Abhishek Saha9850157207M109Kolkata
1279Rushang Desai9850157207M109Mumbai
1280Arvin Kumar8892340054M119Bangalore

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;
SQL Server Select Distinct on one column

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_codeemp_location
119Kolkata
109Bangalore
121Bangalore
119Kolkata
109Mumbai
121Kolkata
121Mumbai
109Kolkata
109Mumbai
119Bangalore

And the below statement will returns unique dept_code and emp_location.

SELECT DISTINCT
  dept_code,
  emp_location
FROM 
  employees;
SQL Server Distinct Multiple Column example

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;
sql server distinct as value count

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.

table employees

The following query will find the distinct phone no of employees.

select distinct
  emp_phone
from
  employees;
sql server distinct with null value example

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.

Advertisement