Microsoft SQL Server 2012 Bible

Select Distinct

The first predicate option in the SELECT command is the keyword DISTINCT, which eliminates duplicate rows from the result set of the query. The duplications are based only on the output columns, not the underlying tables. The opposite of DISTINCT is ALL. Because ALL is the default, it is typically not included.

The following example demonstrates the difference between DISTINCT and ALL. Joins are explained in Chapter 9 but here the JOIN between product and salesorderdetails generates a row each time a product is sold as part of an order. Because this select statement returns only the productname column, it's a perfect example of duplicate rows for the DISTINCT predicate:

USE AdventureWorks;
SELECT ALL p.Name
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail so
    ON p.ProductID = so.ProductID

Result:

Name
--------------------------------------------------
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
Sport-100 Helmet, Red
. . .

With the DISTINCT predicate:

USE AdventureWorks;
SELECT DISTINCT p.Name
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail so
    ON p.ProductID = so.ProductID;

Result:

Name ---------------------------------- Sport-100 Helmet, Red Sport-100 Helmet, Black Mountain Bike Socks, M Mountain Bike Socks, L Sport-100 Helmet, Blue AWC Logo Cap ...