Summary -

In this topic, we described about the MINUS Operator with detailed example.

MINUS operator is used with two SELECT statements. MINUS operator is used to get DISTICT rows in the first SELECT statement which are not present in the second SELECT statement on result set. In simple words it omitted the common rows in the both SELECT statements and duplicate rows in the first SELECT statement. MINUS operator works on ORACLE database only. In MINUS operator the number of columns and DATA TYPE of columns should be same in both SELECT statements.

Syntax -

SELECT Expression1 [AS Alias_name],Expressio2,… 
FROM table1 [WHERE condition]
MINUS
SELECT Expression1, Expressio2,… FROM table2 [WHERE condition];

Example –

let us consider employee_details and employee_info tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee10 Manager Employee6 2019-07-14 28000.00 2000
011 Employee11 Analyst Employee7 2019-09-05 12000.00 2000

employee_info -

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting First table rows with multiple columns Which are not in second table by using MINUS operator.

Requirement – Getting employee_details table rows of multiple columns which are not in employee_info table by using MINUS operator. The query was as follows – 

SELECT emp_name, designation, manager_id FROM employee_details
MINUS 
SELECT emp_name, designation, manager_id FROM employee_info;

By executing above query we can get rows of multiple columns from employee_details which are not in employee_info. The output was as follows -

emp_name designation manager_id
Employee1 Director
Employee3 Manager Employee1
Employee5 Analyst Employee3
Employee6 Analyst Employee3
Employee7 Clerk Employee3
Employee10 Manager Employee6
Employee11 Analyst Employee7

Scenario – Getting First table rows of single column Which are not in second table by using MINUS operator.

Requirement – Getting employee_details table rows of single column which are not in employee_info table by using MINUS operator. The query was as follows – 

SELECT  emp_name FROM employee_details
MINUS
SELECT emp_name FROM employee_info;

By executing above query we can get rows from employee_details which are not in employee_info. The output was as follows -

emp_name
Employee1
Employee3
Employee5
Employee6
Employee7
Employee10
Employee11

Scenario – Getting First table rows with multiple columns Which are not in second table by using MINUS operator with WHERE condition.

Requirement – Getting employee_details table rows of multiple columns which are not in employee_info table by using MINUS operator with WHERE condition . The query was as follows – 

SELECT emp_name, designation,manager_id FROM employee_details
 WHERE dept_id=2000
MINUS
SELECT emp_name, designation, manager_id FROM employee_info
 WHERE dept_id=2000;

By executing above query we can get rows of multiple columns whose dept_id is 2000 from employee_details which are not in employee_info. The output was as follows -

emp_name designation manager_id
Employee10 Manager Employee6
Employee11 Analyst Employee7