Oracle DISTINCT clauses

In this tutorial, you will learn how to use Oracle SELECT DISTINCT statements to query different data from a table.

Introduction to Oracle SELECT DISTINCT statements

Use clauses in a SELECT statement DISTINCT to filter duplicate rows in the result set. It ensures that the value of the SELECT specified one or more columns is unique in the clause.

The SELECT DISTINCT syntax of a statement is described below:

SELECT DISTINCT  column_1FROM  table_name;

In the above syntax, the table_name values in the table's column_1 columns are compared to filter duplicates.

To retrieve unique data based on multiple columns, you only need to SELECT specify the list of columns in the clause, as follows:

SELECT    DISTINCT column_1,    column_2,        ...FROM    table_name;

In this syntax, column_1 a column_2 column_n combination of the values in and is used to determine the uniqueness of the data.

DISTINCTClauses can only be SELECT used in statements.

Note that this DISTINCT is not a synonym for the SQL standard UNIQUE . It DISTINCT UNIQUE is a good habit to always use and not to use.

Oracle DISTINCT sample

Here's a look at SELECT DISTINCT some examples of how to use it to see how it works.

1. Examples of Oracle distinct applied on a single column

View the contact () table in the sample database contacts :

The following example retrieves the names of all the contacts:

SELECT first_nameFROM contactsORDER BY first_name;

Execute the above query statement to get the following results-

The query returns 319 rows, indicating that the contact ( contacts ) table has 319 rows.

To get a unique contact name, you can add the keyword DISTINCT to the statement above SELECT , as follows:

SELECT DISTINCT first_nameFROM contactsORDER BY first_name;

Execute the above query statement to get the following results-

The query returns 302 rows, indicating that the contact ( contacts ) table has 17 rows that are duplicates and that they have been filtered.

2. Oracle distinct application multi-column example

Look at the order_items table below, the structure of the table is as follows:

The following statement order_items selects a different product ID and quantity from the table:

SELECT    DISTINCT product_id,    quantityFROM    ORDER_ITEMSORDER BY product_id;

Execute the above query statement to get the following results-

In this example, product_id and quantity the values of the columns are used to evaluate the uniqueness of the rows in the result set.

3. Oracle distinct and NULL

DISTINCTNULLtreats a value as a duplicate value. If you use SELECT DISTINCT a statement to query data from a column that has more than one NULL value, the result set contains only one NULL value.

See the table in the sample database locations , as shown in the following structure-

The following statement state retrieves data from a column that has multiple NULL values:

SELECT DISTINCT stateFROM locationsORDER BY state NULLS FIRST;

Execute the above example code to get the following results-

As you can see, only one value is returned NULL .

In this tutorial, you learned how to use SELECT DISTINCT statements to get unique data based on one or more columns of filtering.

Oracle DISTINCT clauses