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.

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.

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