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!
