This Oracle tutorial explains how to use the Oracle DISTINCT clause with syntax and examples.
The Oracle DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used with SELECT statements.
The syntax for the DISTINCT clause in Oracle/PLSQL is:
SELECT DISTINCT expressions
Parameters or Arguments
The columns or calculations that you wish to retrieve.
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
Optional. The conditions that must be met for the records to be selected.
- When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
- When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
- In Oracle, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.
Example - With Single Expression
Let's look at the simplest Oracle DISTINCT clause example. We can use the Oracle DISTINCT clause to return a single field that removes the duplicates from the result set.
SELECT DISTINCT state
WHERE last_name = 'Smith';
This Oracle DISTINCT example would return all unique state values from the customers table where the customer's last_name is 'Smith'.
Example - With Multiple Expressions
Let's look at how you might use the Oracle DISTINCT clause to remove duplicates from more than one field in your SELECT statement.
SELECT DISTINCT city, state
WHERE total_orders > 10
ORDER BY city;
This Oracle DISTINCT clause example would return each unique city and state combination from the customers table where the total_orders is greater than 10. The results are sorted in ascending order by city.
In this case, the DISTINCT applies to each field listed after the DISTINCT keyword, and therefore returns distinct combinations.