Oracle DISTINCT Keyword

This SQL tutorial focuses on the SQL DISTINCT keyword in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:

• Writing Basic SQL SELECT statements
• SQL Arithmetic Operators
• SQL Column Aliases
• SQL String Concatenation
• SQL DISTINCT keyword

The Oracle DISTINCT Keyword

The default display of Oracle queries is all rows, including duplicate rows. The Oracle DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values.

For example: let us assume we have a Customers table, which contains a list of customers from different cities: 40 customers from London, 30 from Liverpool and 30 from Manchester. You were asked to display the list of cities where these customers reside:

SELECT city
FROM   customers

This Oracle statement is not very helpful since it retrieves 100 rows (London returns 40 times, Liverpool returns 30 times, and Manchester returns 30 times), whereas our intention was to display only 3 lines.

To resolve this issue and retrieve only unique values, use the Oracle DISTINCT keyword:

SELECT DISTINCT column_name
FROM     table_name 

SELECT DISTINCT city
FROM   customers

The result:

CITY
------
London
Liverpool
Manchester

Note : The Oracle DISTINCT keyword should be included immediately after the Oracle SELECT keyword.

Oracle DISTINCT Statement on Multiple Columns

After the Oracle DISTINCT keyword, it is possible to specify more than one column. The Oracle DISTINCT qualifier affects all selected columns, in so doing, the result will display the unique combination of values from the different columns. For example, a list of customers from different cities and countries:

You can see that there are two customers from Liverpool-England, two customers from Paris-France, and another customer from London-England.

Executing the Oracle DISTINCT statement on the Country column results in retrieving 2 rows (England,Paris).

SELECT DISTINCT country
FROM            customers

Executing the Oracle DISTINCT statement on the City column results in retrieving 3 rows (Liverpool, London, paris).

SELECT DISTINCT city 
FROM            customers

If you execute the Oracle DISTINCT statement on both the City and Country columns:

SELECT DISTINCT city , country
FROM            customers

Three results are retrieved – Liverpool England, London England, Paris France.