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 AdventureWorks2008R2;
GO
SELECT DISTINCT ProductID
FROM Production.ProductInventory ;
This query returns 432 rows.
Important
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.
Note
For compatibility with the ISO standard and other implementations of SQL Server, the ALL keyword can explicitly ask for all rows. However, there is no need to specify ALL because it is the default.