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.
EmpId | FirstName | LastName | 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 |
EmpId | FirstName | LastName | 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.
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 | 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.
SELECT EmpId, FirstName FROM Employee MINUS SELECT * from Employee_backup
You can use the WHERE clause with any or all queries, as shown below.
SELECT * FROM Employee MINUS SELECT * from Employee_backup WHERE Salary > 15000
EmpId | FirstName | LastName | 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 |