SQL MINUS

Summary: in this tutorial, you will learn how to use the SQL MINUS operator to subtract one result set from another.

Introduction to SQL MINUS operator

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;Code language: SQL (Structured Query Language) (sql)

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.

The following picture illustrates the MINUS operator.

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.

SQL MINUS examples

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;Code language: SQL (Structured Query Language) (sql)

SQL  MINUS with ORDER BY example

To 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;Code language: SQL (Structured Query Language) (sql)

Now you should have a good understanding of the SQL MINUS operator and know how to apply it to compare two result sets.