Elimination of unnecessary DISTINCT conditions

Sometimes a DISTINCT condition is unnecessary. For example, the properties of one or more column in your result may contain a UNIQUE condition, either explicitly or implicitly, because it is a primary key.

Examples

The DISTINCT keyword in the following command is unnecessary because the Products table contains the primary key p.ID, which is part of the result set.

SELECT DISTINCT p.ID, p.Quantity
FROM Products p;

Products<seq>

The database server executes the semantically-equivalent query:

SELECT p.ID, p.Quantity
FROM Products p;

Similarly, the result of the following query contains the primary keys of both tables, so each row in the result must be distinct. So, the database server executes this query without performing DISTINCT on the result set.

SELECT DISTINCT *
FROM SalesOrders o JOIN Customers c
   ON o.CustomerID = c.ID
WHERE c.State = 'NY';

Work[ HF[ c<seq> ] *JH o<seq> ]

Discuss this page in DocCommentXchange.