ASA SQL User's Guide
Query Optimization and Execution
Semantic query transformations
Types of semantic transformations
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 in fact a primary key.
The distinct keyword in the following command is unnecessary because the product table contains the primary key p.id, which is part of the result set.
SELECT DISTINCT p.id, p.quantity FROM product p
p<seq>
The database server actually executes the semantically equivalent query:
SELECT p.id, p.quantity FROM product p
Similarly, the result of the following query contains the primary keys of both tables so each row in the result must be distinct.
SELECT DISTINCT * FROM sales_order o JOIN customer c ON o.cust_id = c.id WHERE c.state = 'NY'
c<seq> JNL o<ix_sales_cust>