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.