syntax and example of distinct clause in oracle database query

The DISTINCT clause in SQL / PLSQL allows us to remove duplicate records from the result of a query. It can be used only with a SELECT statement.

Syntax for the DISTINCT clause in PLSQL is:

SELECT DISTINCT column_name(s)
FROM table_name
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Here we can see that ‘Sales’ and ‘IT’ departments occurs twice in the department column of ‘employee’ table.
If we wish to view only unique departments from the employee table we can achieve the same by using the DISTINCT clause as:

SELECT DISTINCT department
FROM employee;

The result of the above query will be:

Department

Sales

Support

Here we have retrieved only unique department names from the ‘employee’ table and eliminated the duplicates using DISTINCT clause.

Scenario 2:

DISTINCT clause can also be used with combination of columns.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we can see that we have 2 occurrences of ‘Sales’ department and the corresponding commission for them is 10.
If we want to get all the unique departments along with their commissions we can achieve the same as:

SELECT DISTINCT department
                ,commission
FROM employee;

Once we have run the above code following will be the result:

Department	Commission
Sales	10
IT	20
Support

Here we have successfully retrieved the unique combination of columns using the DISTINCT clause.