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
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
The following illustrates the syntax of the
SELECT DISTINCT statement:
SELECT DISTINCT column_1 FROM table;
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;
In this syntax, the combination of values in the
column_3 are used to determine the uniqueness of the data.
DISTINCT clause can be used only in the
DISTINCT is synonym of
UNIQUE which is not SQL standard. It is a good practice to always use
DISTINCT instead of
SELECT DISTINCT examples
Let’s look at some examples of using
SELECT DISTINCT to see how it works.
SELECT DISTINCT one column example
contacts table in the sample database:
The following example retrieves all contact first names:
SELECT first_name FROM contacts ORDER BY first_name;
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;
Now, the result set has 302 rows, meaning that 17 duplicate rows have been removed.
SELECT DISTINCT multiple columns example
See the following
The following statement selects distinct product id and quantity from the
SELECT DISTINCT product_id, quantity FROM ORDER_ITEMS ORDER BY product_id;
The following illustrates the result:
In this example, both values the
quantity columns are used for evaluating the uniqueness of the rows in the result set.
SELECT DISTINCT and NULL
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.
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;
As you can see, only one NULL values 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.