MINUS in Oracle SQL – PLSQL

A MINUS in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a MINUS returns only those rows which are found in the first SELECT statement but are not found in the second SELECT statement.

Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the MINUS to work.

Syntax for MINUS in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
MINUS
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

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

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand MINUS, suppose we write our SQL query using MINUS as:

SELECT employee_id
FROM employee
MINUS
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
101

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.

The record for ‘employee_id’ = ‘101’ is present in employee table but is not present in ‘comm’ table and hence is placed in the result.

Suppose we change our SQL query for MINUS as:

SELECT emp_id
FROM comm
MINUS
SELECT employee_id
FROM employee;

The result of the above query will be:

Emp_Id
106
107

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.
The record for ‘employee_id’ = ‘101’ is present in employee table(second table) but is not present in ‘comm’ table (first table)and hence is not placed in the result.


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