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)
Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.
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:
Here we have retrieved only unique department names from the ‘employee’ table and eliminated the duplicates using DISTINCT clause.
DISTINCT clause can also be used with combination of columns.
Let’s assume that we have a new table namely ‘employee’ as shown below:
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:
Here we have successfully retrieved the unique combination of columns using the DISTINCT clause.