Oracle SELECT DISTINCT

Summary: in this tutorial, you will learn how to use the Oracle SELECT DISTINCT statement to query distinct data from tables.

Introduction to Oracle SELECT DISTINCT statement

The DISTINCT clause is used in a SELECT statement to filter duplicate rows in the result set. It ensures that rows returned are unique for the column or columns specified in the SELECT clause.

The following illustrates the syntax of the SELECT DISTINCT statement:

SELECT DISTINCT column_1
FROM table;Code language: SQL (Structured Query Language) (sql)

In this statement, the values in the column_1 of the table are compared to determine the duplicates.

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

SELECT
    DISTINCT column_1,
    column_2,
    column_3
FROM
    table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, the combination of values in the column_1,  column_2, and column_3 are used to determine the uniqueness of the data.

The DISTINCT clause can be used only in the SELECT statement.

Note that DISTINCT is a synonym of UNIQUE which is not SQL standard. It is a good practice to always use DISTINCT instead of UNIQUE.

Oracle SELECT DISTINCT examples

Let’s look at some examples of using SELECT DISTINCT to see how it works.

A) Oracle SELECT DISTINCT one column example

See the contacts table in the sample database:

The following example retrieves all contact first names:

SELECT
  first_name
FROM
  contacts
ORDER BY
  first_name; Code language: SQL (Structured Query Language) (sql)

The query returned 319 rows, indicating that the contacts table has 319 rows.

To get unique contact first names, you add the DISTINCT keyword to the above SELECT statement as follows:

SELECT DISTINCT
  first_name
FROM
  contacts
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Now, the result set has 302 rows, meaning that 17 duplicate rows have been removed.

B) Oracle SELECT DISTINCT multiple columns example

See the following order_items table:

The following statement selects distinct product id and quantity from the order_items table:

SELECT
    DISTINCT product_id,
    quantity
FROM
    ORDER_ITEMS
ORDER BY
    product_id;Code language: SQL (Structured Query Language) (sql)

The following illustrates the result:

In this example, both values the product_id and quantity columns are used for evaluating the uniqueness of the rows in the result set.

C) Oracle SELECT DISTINCT and NULL

The DISTINCT treats NULL values to be duplicates of each other. If you use the SELECT DISTINCT statement to query data from a column that has many NULL values, the result set will include only one NULL value.

See the locations table in the sample database.

The following statement retrieves data from the state column, which has many NULL values:

SELECT
    DISTINCT state
FROM
    locations
ORDER BY
    state NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

Result:

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

Note that if you want to apply the DISTINCT to some columns, while skipping other columns, you should use the GROUP BY clause instead.

In this tutorial, you have learned how to use the SELECT DISTINCT statement to get unique data based on one or more columns.