Using MINUS Operator in Oracle

This article is written about how to use the Oracle MINUS operator with syntax and examples.

Description

The Oracle MINUS operator is used to return all rows in the first SELECT statement that are no longer again via the 2nd SELECT statement. Each SELECT assertion will define a dataset. The MINUS operator will retrieve all information from the first dataset and then eliminate from the outcomes all data from the second dataset.

Minus Query

Explanation: The MINUS query will return the records in the blue shaded area. These are the records that exist in Dataset1 and no longer in Dataset2.

Each SELECT assertion within the MINUS question ought to have the same variety of fields in the result units with similar data types.

Syntax

The syntax for the MINUS operator in Oracle/PLSQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

expression1, expression2, … expression_n The columns or calculations that you want to retrieve. tables The tables that you desire to retrieve archives from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the archives to be selected.

Note

There have to be identical wide variety of expressions in both SELECT statements and have similar data types.

Example – With Single Expression

The following is an Oracle MINUS operator example that returns one field with the identical facts type:

SELECT supplier_id
FROM suppliers
MINUS
SELECT supplier_id
FROM orders;

This Oracle MINUS instance returns all supplier_id values that are in the suppliers desk and now not in the orders table. What this means is that if a supplier_id value existed in the suppliers desk and additionally existed in the orders table, the supplier_id value would now not appear in this end result set.

Example – Using ORDER BY

The following is a MINUS operator example that makes use of an ORDER BY clause:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'Florida'
MINUS
SELECT company_id, company_name
FROM companies
WHERE company_id <= 400
ORDER BY 2;

In this MINUS example, seeing that the column names are exceptional between the two SELECT statements, it is greater tremendous to reference the columns in the ORDER BY clause through their position in the end result set. In this example, we have sorted the effects via supplier_name / company_name in ascending order, as denoted by means of the ORDER BY 2

The supplier_name / company_name fields are in role #2 in the end result set.