Select Distinct in Oracle

Posted by AbundantCode

This article will show you how to query distinct data from tables using the Oracle SELECT DISTINCT statement.

In a SELECT statement, the DISTINCT clause is used to filter duplicate rows from the result set. It assures that the rows returned are unique for the SELECT clause’s given column or columns.

The syntax of the SELECT DISTINCT statement is shown below:

SELECT DISTINCT column_1
FROM table;

The values in column 1 of the table are compared in this statement to find duplicates.

You only need to specify the column list in the SELECT clause to receive unique data based on many columns:

SELECT
    DISTINCT column_1,
    column_2,
    column_3
FROM
    table_name;

The combination of values in column 1, column 2, and column 3 is utilised in this syntax to determine the data’s uniqueness.

Only the SELECT statement can employ the DISTINCT clause.

It’s worth noting that DISTINCT is a synonym for UNIQUE, which isn’t a SQL standard. It’s best to utilise DISTINCT instead than UNIQUE wherever possible.

SELECT DISTINCT examples in Oracle

Let’s have a look at some examples of how to use SELECT DISTINCT.

One-column Oracle SELECT DISTINCT sample

The following code retrieves the first names for all contacts:

SELECT
  first_name
FROM
  contacts
ORDER BY
  first_name;

To get unique contact first names, use the DISTINCT term in the SELECT statement above:

SELECT DISTINCT
  first_name
FROM
  contacts
ORDER BY
  first_name;

Example of Oracle SELECT DISTINCT multiple columns

From the order items table, the following statement picks distinct product id and quantity:

SELECT
    DISTINCT product_id,
    quantity
FROM
    ORDER_ITEMS
ORDER BY
    product_id;

SELECT DISTINCT and NULL in Oracle

NULL values are treated as duplicates by the DISTINCT. The result set will only contain one NULL value if you use the SELECT DISTINCT command to query data from a column with numerous NULL values.

The following statement obtains information from the state column, which has a large number of NULL values:

SELECT
    DISTINCT state
FROM
    locations
ORDER BY
    state NULLS FIRST;

Only one NULL value is returned, as you can see.

You should use the GROUP BY clause instead of the DISTINCT clause if you want to apply the DISTINCT to some columns while skipping others.

Tags: