Table of Contents

Introduction

SQL Server SELECT DISTINCT clause returns unique rows from a query result by removing duplicate combinations of the selected values. It is useful when a table contains repeated values and you need a list of distinct departments, cities, product categories, customer names, or other attributes.

DISTINCT affects the result set only. It does not delete or modify duplicate records stored in the underlying table. SQL Server compares the values in the select list and returns one row for each unique combination.

SQL Server SELECT DISTINCT Syntax

SELECT DISTINCT column_name
FROM table_name;

SELECT DISTINCT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

Place DISTINCT immediately after SELECT. You can use it with one column or multiple expressions. The optional WHERE clause filters source rows, while ORDER BY sorts the final distinct result.

Sample Employees Table

Consider an Employees table with the following sample records:

EmployeeID EmployeeName Department City Salary
1 Rahim Sales Dhaka 35000
2 Karim IT Dhaka 50000
3 Sumaiya Sales Chattogram 42000
4 Nadia IT Dhaka 55000
5 Hasan HR Khulna 38000
6 Rupa Sales Dhaka 39000

SELECT DISTINCT on a Single Column

To retrieve each department only once, select the Department column with DISTINCT:

SELECT DISTINCT Department
FROM Employees;

Result:

Department
----------
HR
IT
Sales

Although multiple employees work in Sales and IT, each department appears once in the result. The EmployeeID and other unselected columns do not affect this comparison.

SELECT DISTINCT on Multiple Columns

When you select more than one column, SQL Server checks the complete combination of selected values. For example:

SELECT DISTINCT Department, City
FROM Employees;

Result:

Department  City
----------  ------------
HR          Khulna
IT          Dhaka
Sales       Chattogram
Sales       Dhaka

The two Sales records in Dhaka produce one row, but Sales in Chattogram remains a separate combination. DISTINCT does not make each column independently unique; it makes each selected row combination unique.

If you include EmployeeID in the select list, records with different employee IDs will normally remain separate:

SELECT DISTINCT EmployeeID, Department
FROM Employees;

SELECT DISTINCT with WHERE Clause

Use WHERE to filter records before SQL Server removes duplicate result rows:

SELECT DISTINCT City
FROM Employees
WHERE Department = 'Sales';

This query returns the unique cities where Sales employees work. Only rows that satisfy the WHERE condition participate in the DISTINCT operation.

SELECT DISTINCT with ORDER BY

Use ORDER BY to sort unique values in ascending or descending order:

SELECT DISTINCT Department
FROM Employees
ORDER BY Department ASC;

For descending order:

SELECT DISTINCT Department
FROM Employees
ORDER BY Department DESC;

SQL Server requires ORDER BY expressions in a SELECT DISTINCT query to appear in the select list. If you need to sort by a value that is not part of the distinct output, use a derived table or another query layer and define the intended sorting logic there.

SELECT DISTINCT with NULL Values

SQL Server treats NULL values as one distinct value for duplicate elimination. If several rows contain NULL in the selected column, the DISTINCT result contains a single NULL row:

SELECT DISTINCT ManagerID
FROM Employees;

This behavior is useful when listing all known manager IDs while retaining one NULL entry for employees whose manager is not assigned. Remember that NULL represents an unknown or missing value, so compare it with IS NULL or IS NOT NULL, not with the equals operator.

SELECT DISTINCT with Expressions and Aliases

DISTINCT can be applied to calculated expressions. SQL Server removes duplicates based on the expression result:

SELECT DISTINCT UPPER(City) AS CityName
FROM Employees
ORDER BY CityName;

You can also use string or numeric expressions when the expression is valid in the select list:

SELECT DISTINCT
    Department,
    Salary * 12 AS AnnualSalary
FROM Employees
ORDER BY Department, AnnualSalary;

Different source values that evaluate to the same selected expression result can be returned as one row.

SELECT DISTINCT with JOIN

A one-to-many JOIN can return the same parent value multiple times because one parent row matches several child rows. DISTINCT can return each selected department name once:

SELECT DISTINCT d.DepartmentName
FROM Employees AS e
INNER JOIN Departments AS d
    ON e.DepartmentID = d.DepartmentID;

Use DISTINCT when the requested output is genuinely a unique list. If duplicates appear because a JOIN condition is missing or incorrect, fix the join keys or table relationships instead of using DISTINCT to hide the problem.

SELECT DISTINCT with COUNT

Use COUNT(DISTINCT expression) to count unique non-NULL values in a column:

SELECT COUNT(DISTINCT Department) AS UniqueDepartmentCount
FROM Employees;

The result is the number of different departments. COUNT(DISTINCT column) does not count NULL values.

To count unique combinations of two or more columns, use a DISTINCT subquery:

SELECT COUNT(*) AS UniqueDepartmentCityCount
FROM (
    SELECT DISTINCT Department, City
    FROM Employees
) AS UniqueDepartmentCities;

SELECT DISTINCT with Aggregate Functions

DISTINCT can also be used inside aggregate functions to aggregate unique values rather than every row. For example, this calculates the total of unique salary amounts:

SELECT SUM(DISTINCT Salary) AS TotalUniqueSalary
FROM Employees;

Likewise, AVG(DISTINCT Salary) calculates the average of distinct salary values. This is different from using SELECT DISTINCT on the final result set: the DISTINCT keyword inside an aggregate applies to the values passed into that aggregate.

SELECT DISTINCT with TOP

Combine DISTINCT with TOP when you need a limited number of unique results:

SELECT DISTINCT TOP (3) City
FROM Employees
ORDER BY City ASC;

ORDER BY is important when you need a predictable selection of the first three values. Without an ordering requirement, SQL Server does not guarantee which qualifying values TOP will return.

SELECT DISTINCT with UNION and UNION ALL

UNION combines result sets and removes duplicate rows across them. UNION ALL combines result sets while retaining duplicates:

SELECT City
FROM Customers
UNION
SELECT City
FROM Suppliers;

Because UNION already removes duplicate rows, adding DISTINCT to each individual SELECT is usually unnecessary unless it changes another part of the query. Use UNION ALL when duplicate rows are meaningful or when you want to avoid the extra duplicate-elimination work.

SELECT DISTINCT vs GROUP BY

For a simple list of unique selected values, DISTINCT and GROUP BY can return the same rows:

SELECT DISTINCT Department
FROM Employees;

SELECT Department
FROM Employees
GROUP BY Department;

Choose DISTINCT when your main goal is duplicate elimination. Choose GROUP BY when you need one result per group together with aggregate calculations:

SELECT
    Department,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

GROUP BY expresses the grouping operation clearly and supports aggregates such as COUNT, SUM, AVG, MIN, and MAX.

SELECT DISTINCT vs SELECT ALL

SQL Server uses ALL as the default behavior when DISTINCT is not specified. SELECT ALL keeps duplicate rows, while SELECT DISTINCT removes duplicate selected row combinations:

SELECT ALL Department
FROM Employees;

SELECT DISTINCT Department
FROM Employees;

You rarely need to write ALL explicitly because a normal SELECT already returns duplicates unless DISTINCT is specified.

SELECT DISTINCT with Subqueries and CTEs

You can place a DISTINCT query inside a derived table or common table expression (CTE) to reuse the unique result in another query:

WITH UniqueDepartments AS (
    SELECT DISTINCT Department
    FROM Employees
)
SELECT Department
FROM UniqueDepartments
WHERE Department <> 'HR'
ORDER BY Department;

This separates the duplicate-removal step from later filtering or joining logic and can make complex queries easier to read.