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

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

Oracle MINUS examples

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

contacts table
employees table

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

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

Oracle MINUS lastnames example

See the following products and inventories tables:

products table

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

Here is the result:

Oracle MINUS example

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.

Was this tutorial helpful?