Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle MINUS

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

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;

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;

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?
  • YesNo
Previous Oracle INTERSECT
Next Oracle Subquery

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.