Tutorialsteacher

Follow Us

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
EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14
Employee_backup
EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
10'Swati''Karia''[email protected]'160002018-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.

EmpIdFirstNameLastNameEmailSalaryHireDate
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-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
EmpIdFirstNameLastNameEmailSalaryHireDate
2'James''Bond'2018-07-29
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14