SELECT DISTINCT

When we use SELECT statement to retrieve rows, it may also include duplicate rows. We use SELECT DISTINCT statement to filter all the duplicate rows and to get only unique values.

The syntax of SELECT DISTINCT statement is as follows:

SELECT DISTINCT column_1
FROM table;

Using this query, we get only unique rows from column_1. We can also use multiple columns in this query to get unique rows based on those columns.

Lets understand this using an example,

If we want to find unique rows across all there columns, then we use this query:

SELECT DISTINCT column_1, column_2, column_4 
FROM table_1;

Result:

As only row 2 and row 3 were exact duplicates of each other, only row with ROW_ID 3 was eliminated in the results.

SELECT DISTINCT Examples:

1. Oracle SELECT DISTINCT from one column

We are using Employees table for this example,

Lets use simple SELECT query to get all the departments from Employee table.

SELECT department_id FROM employees;

Result:

As we can see, we get 50 rows in result with a lot of duplicate rows.

Lets use SELECT DISTINCT statement to get only unique rows:

SELECT DISTINCT department_id FROM employees;

Result:

The rows in result are reduced to 12 and now only include unique rows.

2. Oracle SELECT distinct from multiple columns

Using the same table, lets use a simple SELECT query on multiple columns in employees table.

SELECT job_id, department_id FROM employees;

Result:

We get 50 rows in the result. Now lets use SELECT DISTINCT :

SELECT DISTINCT job_id, department_id 
FROM employees ORDER BY department_id;

Result:

We use ORDER BY to sort the result in ascending order according to values in department_id column. As we can see, the rows in result got reduced to 20 and now we have rows which are unique after combining both the columns.

3. Oracle SELECT DISTINCT on NULL values

NULL is a special value in Oracle which represents absence of value rather than presence of value. For this reason, NULL is not even equal to NULL in Oracle.

DISTINCT treats multiple NULL values as duplicates of each other and will only return ONE NULL value in the result.

If we see the commission_pct column in Employee table then it contains multiple NULL values. If we use a simple SELECT query we get the following result:

SELECT commission_pct FROM employees;

Result:

It returns 50 rows. Now, Lets use SELECT DISTINCT:

SELECT DISTINCT commission_pct FROM employees;

Result:

We get only 8 rows in result with only one NULL value.

In this article, we learned how to use SELECT DISTINCT statement to filter duplicate rows and get only UNIQUE values.