MINUS Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

MINUS Operator in Oracle with Examples

In this article, I am going to discuss MINUS Operator in Oracle with Examples. Please read our previous article, where we discussed INTERSECT Operator in Oracle with Examples. At the end of this article, you will understand MINUS Operator in detail with Examples.

MINUS Operator in Oracle

The MINUS operator in Oracle is used to return unique rows from the left query which isn’t present in the right query’s results. That means the MINUS Operator takes the result set of the first select statement and removes those rows that are returned by a second select statement. Following is the pictorial representation of the MINUS Operator.

Following is the syntax of the MINUS operator.

Examples to understand MINUS Operators in Oracle:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the MINUS SET Operator in Oracle.

Please use the below SQL Script to create the EmployeeUK and EmployeeUSA tables with the required data.

SET linesize 300;
DROP Table EmployeeUK;
DROP Table EmployeeUSA;

CREATE TABLE EmployeeUK
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUK VALUES(1, 'Pranaya', 'Rout', 'Male','IT');
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUK VALUES(3, 'Preety', 'Tiwary', 'Female','HR');
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUK VALUES(5, 'Anurag', 'Mohanty', 'Male','IT');
INSERT INTO EmployeeUK VALUES(6, 'Rajesh', 'Pradhan', 'Male','HR');
INSERT INTO EmployeeUK VALUES(7, 'Hina', 'Sharma', 'Female','IT');

CREATE TABLE EmployeeUSA
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUSA VALUES(1, 'James', 'Pattrick', 'Male','IT');
INSERT INTO EmployeeUSA VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUSA VALUES(3, 'Sara', 'Taylor', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(5, 'Sushanta', 'Jena', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(6, 'Mahesh', 'Sindhey', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(7, 'Hina', 'Sharma', 'Female','IT');

MINUS Operator Example in Oracle:

The following SQL Query will return the unique rows from the left query (the select statement before the MINUS operator) that is not present in the right query (the select statement after the MINUS operator).

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
MINUS
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

The above statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second. Once you execute the above MINUS Operator query, you will get the following result set. Please observe, here we got only 4 rows in the result set which are present in the first result set but not in the second result set.

To retrieve all of the rows from Table EmployeeUSA that do not exist in Table EmployeeUK, reverse the two queries as shown below.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA
MINUS
SELECT FirstName, LastName, Gender, Department FROM EmployeeUK;

When you execute the above query, you will get the following output.

MINUS Operator in Oracle on a single table:

You can also use the MINUS operator on a single table. Let’s use the following Employees table for this example. 

Please use the below SQL Script to create and populate the Employees table with the required data.

CREATE TABLE Employees
(
    Id int,
    Name VARCHAR(20),
    Gender VARCHAR(10),
    Salary int
);

Insert into Employees values (1, 'Mark', 'Male', 52000);
Insert into Employees values (2, 'Mary', 'Female', 55000);
Insert into Employees values (3, 'Steve', 'Male', 45000);
Insert into Employees values (4, 'John', 'Male', 40000);
Insert into Employees values (5, 'Sara', 'Female', 48000);
Insert into Employees values (6, 'Pam', 'Female', 60000);
Insert into Employees values (7, 'Tom', 'Male', 58000);
Insert into Employees values (8, 'George', 'Male', 65000);
Insert into Employees values (9, 'Tina', 'Female', 67000);
Insert into Employees values (10, 'Ben', 'Male', 80000);

Example: Order By clause should be used only once after the last select statement

SELECT Id, Name, Gender, Salary
FROM Employees
WHERE Salary >= 50000
MINUS
SELECT Id, Name, Gender, Salary
FROM Employees
WHERE Salary >= 60000
ORDER BY Name;

When you execute the above query, you will get the following output.

Difference between MINUS and NOT IN Operator Oracle

Now, you may have one question on your mind i.e. what is the difference between MINUS and NOT IN Operator in Oracle. Let’s understand the difference between MINUS and NOT IN operators in Oracle with one example. We are going to use the following two tables (TableA and TableB).

Please use the below SQL Script to create TableA and TableB with the required sample data.

CREATE TABLE TableA
(
  ID INT,
  Name VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(10)
);

INSERT INTO TableA VALUES(1, 'Pranaya', 'Male','IT');
INSERT INTO TableA VALUES(2, 'Priyanka', 'Female','IT');
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR');

CREATE TABLE TableB
(
  ID INT,
  Name VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(10)
);

INSERT INTO TableB VALUES(2, 'Priyanka', 'Female','IT');
INSERT INTO TableB VALUES(3, 'Preety', 'Female','HR');

Example: Using MINUS Operator in Oracle

The following query returns the rows from the left query that are not present in the right query’s results. 

SELECT ID, Name, Gender, Department FROM TableA
MINUS
SELECT ID, Name, Gender, Department FROM TableB;

When you execute the above MINUS query, you will get the following output.

Example: Using NOT IN Operator in Oracle

The same result can also be achieved using the NOT IN operator in Oracle using the following query.

SELECT ID, Name, Gender, Department FROM TableA Where ID NOT IN (Select ID from TableB);

When you execute the above MINUS query, you will get the following output.

So, what is the difference between MINUS and NOT IN operators in Oracle?

The MINUS operator filters duplicate rows and return only DISTINCT rows from the left query that aren’t in the right query’s results, whereas NOT IN Operator in Oracle does not filter the duplicates rows. To understand this, insert the following row into TableA

INSERT INTO TableA VALUES(1, ‘Pranaya’, ‘Male’, ‘IT’);

Now execute the following MINUS query. Notice that we get only the DISTINCT rows in the result set.

SELECT ID, Name, Gender, Department FROM TableA
MINUS
SELECT ID, Name, Gender, Department FROM TableB;

When you execute the above MINUS query, you will get the following output.

Now execute the following query using NOT IN Operator. Notice that the duplicate rows are not filtered in the result set.

SELECT ID, Name, Gender, Department FROM TableA Where ID NOT IN (Select ID from TableB);

When you execute the above MINUS query, you will get the following output.

The MINUS operator in Oracle expects the same number of columns in both the queries, whereas NOT IN, compares a single column from the outer query with a single column from the subquery. In the following example, the number of columns is different.

SELECT ID, Name, Gender, Department FROM TableA
MINUS
SELECT ID, Name, Gender FROM TableB;

The above query would produce the following error.

The NOT IN operator in Oracle compares a single column from the outer query with a single column from the subquery. In the following example, the subquery returns multiple columns.

SELECT ID, Name, Gender, Department FROM TableA Where ID NOT IN (Select ID, Name from TableB);

The above query would produce the following error.

In the next article, I am going to discuss IS NULL Operator in Oracle. Here, in this article, I try to explain MINUS Operator in Oracle with Examples and I hope you enjoy this article.