Allowing Duplicates

You can apply the ALL, UNIQUE, or DISTINCT keywords to indicate whether duplicate values are returned, if any exist. If you do not specify any of these keywords in the Projection clause, all qualifying rows are returned by default.

Keyword

Effect

ALL

Specifies that all qualifying rows are returned, regardless of whether duplicates exist. (This is the default specification.)

DISTINCT

Excludes duplicates of qualifying rows from the result set

UNIQUE

Excludes duplicate. (Here UNIQUE is a synonym for DISTINCT. This is an extension to the ANSI/ISO standard.)

For example, the next query returns all the unique ordered pairs of values from the stock_num and manu_code columns in rows of the items table. If several rows have the same pair of values, that pair appears only once in the result set:

SELECT DISTINCT stock_num, manu_code FROM items 

You can specify DISTINCT or UNIQUE no more than once in each level of a query or subquery. The following example uses DISTINCT in both the query and in the subquery:

SELECT DISTINCT stock_num, manu_code FROM items 
   WHERE order_num =    (SELECT DISTINCT order_num FROM orders
      WHERE customer_num = 120)