Export (0) Print
Expand All

Eliminating Duplicates with DISTINCT

The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement. If DISTINCT is not specified, all rows are returned, including duplicates. For example, if you select all the product IDs in ProductInventory without DISTINCT, 1069 rows are returned.

With DISTINCT, you can eliminate duplicates and see only the unique product IDs:

USE AdventureWorks;
GO
SELECT DISTINCT ProductID
FROM Production.ProductInventory

This query returns 432 rows.

Important noteImportant

The output for statements involving DISTINCT depends on the collation of the column or expression on which the DISTINCT is applied.

For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.

NoteNote

For compatibility with the ISO standard and other implementations of MicrosoftSQL Server, the ALL keyword can explicitly ask for all rows. However, there is no need to specify ALL because it is the default.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft