Oracle SELECT DISTINCT Statement

Before the introduction of the SELECT DISTINCT statement in Oracle, retrieving unique data from tables was not always straightforward. But you can easily extract unique data from tables using the SELECT DISTINCT query in conjunction with conditions specifying the type of data you are retrieving.

The DISTINCT keyword also compares every value of the column(s) included in the result set produced by the query with one another.

Consequently, if you are having difficulty using the Oracle SELECT DISTINCT statement, here is a tip on how you can use it.

How to Use the Oracle SQL SELECT DISTINCT to Query Distinct Data

Using SELECT DISTINCT will get rid of duplicate results in a single column. Consider a sample data table that contains customer data:

Table name = customer

Now, let's say we want to see a list of customer names because we want to get name tags printed or something.

So let's try writing a query without the distinct statement first.

SELECT first_name
   FROM customer

Output

Rick
Khan
Rick
Vino
Khan

There are some duplications. Rick's and Khan's name appears twice. How would we go about eliminating the duplicates? Each name should only be displayed once.

Let's now add the distinct statement to eliminate the duplicates:

SELECT DISTINCT first_name
   FROM customer

Output

Rick
Khan
Vino

You can also get the first_name and last_name and this will display Rick's record as it has a different last name but will display only one record for Khan, because it has the duplicate first and last name. Below is an example:

SELECT DISTINCT first_name, last_name
   FROM customer

Output

Rick        Daniels
Khan        Joe
Rick        Phillip
Vino        Praha

Note: There are some duplications in the first column, though. Rick's name appears twice. But that does not matter as the difference in the second column makes this output unique.

Conclusion

You can use SELECT DISTINCT to search for unique data in tables and remove duplicate rows in the result set.