Basic about MINUS Operator – using and example

This SQL tutorial explains how to use the SQL MINUS operator with syntax and examples.

Description

The SQL MINUS operator is used to return all rows in the first SELECT announcement that are no longer back with the aid of the 2d SELECT statement. Each SELECT assertion will define a dataset. The MINUS operator will retrieve all information from the first dataset and then take away from the effects all documents from the 2d dataset.

Minus Query

Explanation: The MINUS query will return the information in the blue shaded area. These are the documents that exist in Dataset1 and now not in Dataset2.

Each SELECT statement within the MINUS query need to have the identical wide variety of fields in the end result units with similar records types.

Syntax

The syntax for the MINUS operator in SQL 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 wish to retrieve. tables The tables that you want to retrieve documents from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. These are stipulations that ought to be met for the archives to be selected.

Note

There should be same variety of expressions in both SELECT statements. The corresponding expressions should have the identical facts kind in the SELECT statements. For example: expression1 have to be the equal facts type in both the first and 2nd SELECT statement.

Example – With Single Expression

The following is a SQL MINUS operator instance that has one discipline with the equal data type:

SELECT supplier_id
FROM suppliers
MINUS
SELECT supplier_id
FROM orders;

This SQL MINUS example returns all supplier_id values that are in the suppliers desk and now not in the orders table. What this capability is that if a supplier_id fee existed in the suppliers desk and additionally existed in the orders table, the supplier_id fee would now not show up in this end result set.

Example – Using ORDER BY Clause

The following is a MINUS operator instance that uses the ORDER BY clause:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
MINUS
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

In this SQL MINUS operator example, given that the column names are different between the two SELECT statements, it is greater advantageous to reference the columns in the ORDER BY clause by way of their position in the result set. In this example, we’ve got sorted the results via supplier_name / company_name in ascending order, as denoted by way of the ORDER BY two

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