Oracle MINUS

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

Introduction to Oracle MINUS Operator

The Oracle MINUS operator compares two queries and returns distinct rows from the first query that are not output by the second query. In other words, the MINUS operator subtracts one result set from another.

The following illustrates the syntax of the Oracle MINUS operator:

SELECT
    column_list_1
FROM
    T1
MINUS 
SELECT
    column_list_2
FROM
    T2;

Similar to the UNION and INTERSECT operators, the queries above must conform with the following rules:

• The number of columns and their orders must match.
• The data type of the corresponding columns must be in the same data type group such as numeric or character.

Suppose the first query returns the T1 result set that includes 1, 2 and 3. And the second query returns the T2 result set that includes 2, 3 and 4.

The following picture illustrates the result of the MINUS of T1 and T2:

Oracle MINUS examples

See the following contacts and employees tables in the sample database:

The following statement returns distinct last names from the query to the left of the MINUS operator which are not also found in the right query.

SELECT
    last_name
FROM
    contacts
MINUS
SELECT
    last_name
FROM
    employees
ORDER BY
    last_name;

Here are the last names returned by the first query but are not found in the result set of the second query:

See the following products and inventories tables:

The following statement returns a list of product id from the products table, but do not exist in the inventories table:

SELECT
  product_id
FROM
  products
MINUS
SELECT
  product_id
FROM
  inventories;

Here is the result:

In this tutorial, you have learned how to use the Oracle MINUS operator to compare two queries and return the distinct rows from the first query that are not output by the second query.