The SELECT clause of a query statement can include two optional keywords -- ALL or DISTINCT, followed by the projection-list. If the ALL keyword is specified, duplicate rows are not removed from the query result. If the DISTINCT option is specified, all duplicate rows of data values are excluded from the query result. If neither ALL nor DISTINCT are specified, the SELECT clause defaults to ALL.
For example, the query below selects all of the supplier numbers SNO from the PARTSUPP table, including duplicate numbers.
SELECT ALL sno FROM partsupp
The query results are displayed below:
| sno |
|---|
| S1 |
| S1 |
| S2 |
| S3 |
| S1 |
| S1 |
| S1 |
| S1 |
| S2 |
| S4 |
| S4 |
If we replaced the ALL keyword with DISTINCT, the query retrieves the following set of records:
| sno |
|---|
| S1 |
| S2 |
| S3 |
| S4 |
The DISTINCT keyword, along with the aggregate function COUNT, can be used to tally the distinct records satisfying a WHERE clause condition. See Functions and WHERE Clause for more information. The DISTINCT option keyword may only be used once in a SELECT clause.