SQL - Minus Operator

The MINUS operator returns all the records in the first SELECT query that are not returned by the second SELECT query.

Syntax:

SELECT column_name1, column_name2,...
FROM tables
[WHERE Condition];
MINUS
    SELECT column_name1, column_name2, ...
    FROM tables
    [WHERE Condition];

For the demo purpose, we will use the following tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
Employee_backup
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
10 'Swati' 'Karia' '[email protected]' 16000 2018-08-22

Consider the following query with the MINUS operator.

SQL Script: MINUS Operator
SELECT * FROM Employee
MINUS
    SELECT * from Employee_backup

Above, the first query SELECT * FROM Employee will be executed first and then the second query SELECT * from Employee_backup will be executed. The MINUS operator will return only those records from the first query result that does not exist in the second query result. The following is the result of the above query.

EmpId FirstName LastName Email Salary HireDate
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14

Note that both the queries must have the equal number of expressions in their SELECT clause. The following will raise an error.

SQL Script: Error with MINUS
SELECT EmpId, FirstName FROM Employee
MINUS
    SELECT * from Employee_backup

You can use the WHERE clause with any or all queries, as shown below.

SQL Script: MINUS Query
SELECT * FROM Employee
MINUS
    SELECT * from Employee_backup
    WHERE Salary > 15000
EmpId FirstName LastName Email Salary HireDate
2 'James' 'Bond' 2018-07-29
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14