Curriculum
In this tutorial, you will learn how to use the SQL MINUS operator to subtract one result set from another.
Besides the UNION, UNION ALL, and INTERSECT operators, SQL provides us with the MINUS operator that allows you to subtract one result set from another result set.
The following illustrates the syntax of the MINUS operator.
SELECT
id
FROM
A
MINUS
SELECT
id
FROM
B;
To use the MINUS operator, you write individual SELECT statements and place the MINUS operator between them. The MINUS operator returns the unique rows produced by the first query but not by the second one.
To make the result set, the database system performs two queries and subtracts the result set of the first query from the second one.
In order to use the MINUS operator, the columns in the SELECT clauses must match in number and must have the same or, at least, convertible data type.
We often use the MINUS operator in ETL. An ETL is a software component in data warehouse system. ETL stands for Extract, Transform, and Load. ETL is responsible for loading data from the source systems into the data warehouse system.
MINUS operator to make sure that the data has been loaded fully by subtracting data in target system from the data in the source system.Consider the following employees and dependents tables in the sample database.
Each employee has zero or more dependents while each dependent depends on one and only one employees. The relationship between the dependents and employees is the one-to-many relationship.
The employee_id column in the dependents table references to the employee_id column in the employees table.
You can use the MINUS operator to find the employees who do not have any dependents. To do this, you subtract the employee_id result set in the employees table from the employee_id result set in the dependents table.
The following query illustrates the idea:
SELECT
employee_id
FROM
employees
MINUS
SELECT
employee_id
FROM
dependents;
MINUS with ORDER BY exampleTo sort the result set returned by the MINUS operator, you place the ORDER BY clause at the end of the last SELECT statement.
For example, to sort the employees who do not have any dependents, you use the following query:
SELECT
employee_id
FROM
employees
MINUS
SELECT
employee_id
FROM
dependents
ORDER BY employee_id;