SELECT ALL vs. DISTINCT

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.