SQL DISTINCT Clause
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:
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
SELECT apple_variety, number_of_trees_in_production FROM apple
The duplicated records are highlighted in purple:
Adding the DISTINCT clause to the query:
SELECT DISTINCT apple_variety, number_of_trees_in_production FROM apple
Here are the results:
|Red Delicious 2000|
Only the records matching both columns are discarded. The unique combinations of
number_of_trees_in_production are the only records returned, as the scope of the
DISTINCT clause is the entire record.
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|
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!
IN THIS PAGE