SQL Server: DISTINCT Clause

In this post explains how to use the DISTINCT clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used with SELECT statements.

Syntax

The syntax for the DISTINCT clause in SQL Server (Transact-SQL) is:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

Note

• When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
• When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
• In SQL Server, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

Example - With Single Expression

Let's look at the simplest SQL Server DISTINCT clause example. We can use the SQL Server DISTINCT clause to return a single field that removes the duplicates from the result set.

For example:

SELECT DISTINCT last_name
FROM employees
WHERE employee_id >= 50;

This SQL Server DISTINCT example would return all unique last_name values from the employees table where the employee_id is greater than or equal to 50.

Example - With Multiple Expressions

Let's look at how you might use the SQL Server DISTINCT clause to remove duplicates from more than one field in your SELECT statement.

For example:

SELECT DISTINCT first_name, last_name
FROM employees
WHERE employee_id >=50
ORDER BY last_name;

This SQL Server DISTINCT clause example would return each unique first_name and last_name combination from the employees table where the employee_id is greater than or equal to 50. The results are sorted in ascending order by last_name.

In this case, the DISTINCT applies to each field listed after the DISTINCT keyword, and therefore returns distinct combinations.