DISTINCT Clause in Oracle SQL – PLSQL

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
IT
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.


Tagged , , , , , , , , , , , . Bookmark the permalink.