SQL DISTINCT Clause

The SQL DISTINCT clause is used to discard duplicate records from the results of a query. You have seen it in action in the Aggregation and Subqueries lessons. Now it is time to look at more details. From a syntax point of view the DISTINCT clause must be before the first element in the SELECT list of columns:


SELECT  DISTINCT apple_variety
  FROM  apple

As you know from previous lessons, there are several records for the same apple variety. Using the DISTINCT clause ignores the duplicates.

Here is the result of the query:

Apple Variety
Red Delicious
Magic Green
Red Globus

One important point is the DISTINCT clause scope is the full record returned by the SELECT. For multiple columns, you can have duplicates in one column and only discard records with duplicates in both columns.

Here is an example without using the DISTINCT clause:


SELECT  apple_variety, number_of_trees_in_production
  FROM  apple

The duplicated records are highlighted in purple:

apple_variety number_of_trees_in_production
Red Delicious 2000
Magic Green 700
Red Globus 500
Red Delicious 1800
Magic Green 500
Red Globus 500
Red Delicious 1800
Red Globus 500
Red Delicious 1500
Red Delicious 1500

Adding the DISTINCT clause to the query:


SELECT  DISTINCT apple_variety, number_of_trees_in_production
  FROM  apple

Here are the results:

apple_variety number_of_trees_in_production
Red Delicious 2000
Magic Green 700
Red Globus 500
Red Delicious 1800
Magic Green 500
Red Delicious 1500

Only the records matching both columns are discarded. The unique combinations of apple_variety and number_of_trees_in_production are the only records returned, as the scope of the DISTINCT clause is the entire record.

SQL DISTINCT Clause In SQL COUNT() Function

The DISTINCT clause can be used as a sub-clause of the COUNT() function, however the behavior is totally different. The COUNT() function with the DISTINCT is used to count how many unique values are in a column. For example if you want to know how many apple varieties:


SELECT  COUNT (DISTINCT apple_variety) AS “Number of Varieties”
  FROM  apple

The result is:

Number of Varieties
3

Closing Words

In this lesson you learned two different uses of the DISTINCT clause: You can discard duplicate records in the result set, or you can count the number of unique values in a column. In the next lesson we will cover some interesting DATE oriented functions. Keep going, learn SQL and increase your skills!