ASA SQL User's Guide

Query Optimization and Execution

Semantic query transformations

Types of semantic transformations

Unnecessary DISTINCT elimination

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.

Examples 

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>

SQL Anywhere Studio 9.0.2
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.