Using DISTINCT Clause in Oracle

This article is written about how to use the Oracle DISTINCT clause with syntax and examples.

Description

The Oracle DISTINCT clause is used to get rid of duplicates from the end result set. The DISTINCT clause can solely be used with SELECT statements.

Syntax

The syntax for the DISTINCT clause in Oracle/PLSQL is:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions The columns or calculations that you desire to retrieve. tables The tables that you want to retrieve data from. There ought to be at least one table listed in the FROM clause. WHERE conditions Optional. The prerequisites that should be met for the documents to be selected.

Note

When only one expression is supplied in the DISTINCT clause, the query will return the unique values for that expression. When extra than one expression is supplied in the DISTINCT clause, the question will retrieve unique mixtures for the expressions listed. In Oracle, the DISTINCT clause does not pass by NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a wonderful value.

Example – With Single Expression

Let’s seem at the simplest Oracle DISTINCT clause example. We can use the Oracle DISTINCT clause to return a single subject that eliminates the duplicates from the end result set.

For example:

SELECT DISTINCT state
FROM customers
WHERE last_name = 'Smith';

This Oracle DISTINCT instance would return all special kingdom values from the clients desk the place the customer’s last_name is ‘Smith’.

Example – With Multiple Expressions

Let’s appear at how you might use the Oracle DISTINCT clause to remove duplicates from more than one field in your SELECT statement.

For example:

SELECT DISTINCT city, state
FROM customers
WHERE total_orders > 10
ORDER BY city;

This Oracle DISTINCT clause example would return every special town and state aggregate from the customers desk where the total_orders is increased than 10. The results are sorted in ascending order by way of city.

In this case, the DISTINCT applies to each discipline listed after the DISTINCT keyword, and therefore returns wonderful combinations.