We can use the MINUS operation to find the difference between two datasets (or queries) in SQL. As a result, if we have similar datasets we can use MINUS to essentially subtract these datasets. MINUS is considered a set operator just like UNION or UNION ALL.
What exactly does this look like? We can use a Venn diagram to visualize this.
The top query will return all records and subtract any records that match from the bottom query, but no records from the bottom query will be returned.
Using The MINUS Operator In SQL
This makes sense, but how do we apply this to an actual scenario? Let’s use employees as an example. Imagine that we only want to view a list of employees from the Accounting department, but do not belong to any other departments outside of Accounting. We can use the MINUS operator in SQL to get the list.
— Top Query
SELECT FirstName, LastName, Email, Phone, Dept
FROM Employee
WHERE Dept = ‘Accounting’
— Subtract dataset using minus
MINUS
— Bottom Query
SELECT FirstName, LastName, Email, Phone, Dept
FROM Employee
WHERE Dept <> ‘Accounting’
The resulting output of the above query will return us a unique list of values. As a result ,this gives us the employees in accounting that do not belong outside of the department.
Let’s look at one more example. We may want to get a list of unique id’s and view all of our employees that are not managers.
SELECT EmployeeId
FROM Employee
MINUS
SELECT ManagerId
FROM Employee — Return all employees that are not managing
Again, we can see how MINUS can be useful to retrieve the difference between result sets.
There are some restrictions when using any set operations like MINUS. For instance, we cannot use this operation on Long datatype columns. We also cannot specify the order by clause in a MINUS subquery. Refer to your database technical documentation on restrictions.
Note: MINUS is supported by Oracle, MySQL and some others. However, the MINUS operator is not supported by all databases. Refer to your database documentation if MINUS does not work. The EXCEPT keyword is used as the alternate in other databases to accomplish the same task.