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
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.