SQL Tutorial SQL Advanced SQL Database SQL References

SQL - MINUS Clause



The SQL MINUS clause is used to compare the result-sets of two or more SQL SELECT statements. It returns all records from the first result-set that do not appear in the second result-set.

While using MINUS clause, the following criteria must be satisfied:

  • Number of columns in each SELECT statement should equal.
  • Datatype of columns in each SELECT statement should match.
  • Order of columns in each SELECT statement should match.
SQL MINUS
Note: The MINUS clause is not supported in all SQL databases. It can be used in databases such as Oracle. For databases such as SQL Server, MariaDB and PostgreSQL uses the EXCEPT clause to perform this type of query.

Syntax

The syntax for using MINUS clause is given below:

SELECT column1, column2, ... FROM table1
MINUS [ALL]
SELECT column1, column2, ... FROM table2

Note: By default, MINUS clause only takes distinct values. To select duplicate values, MINUS ALL clause can be used.

Example:

Consider database tables called Product and Inventory with the following records:

Table 1: Product table

NameBrandPriceQuantity
AppleDelicious1.540
AppleHarvest1.650
OrangeDelicious2.040
OrangeHarvest1.950
WatermelonLocalFarm4.050
MangoLocalFarm3.560

Table 2: Inventory table

NameBrandPriceQuantity
AppleDelicious1.520
OrangeLocalFarm2.040
WatermelonLocalFarm4.020

  • Using MINUS clause: To get the records of Name column which are not present in both record-sets, the following SQL code can be used:

    SELECT Name FROM Product
    MINUS
    SELECT Name FROM Inventory
    ORDER BY Name;
    

    This will produce the result as shown below:

    Name
    Mango
  • Using MINUS clause with WHERE clause: To fetch the records of Name column which are not present in both record-sets with the specified condition, the following SQL code can be used:

    SELECT Name FROM Product
    MINUS
    SELECT Name FROM Inventory
    WHERE Quantity < 30
    ORDER BY Name;
    

    This will produce the following result:

    Name
    Mango
    Orange
  • Using MINUS clause with multiple columns: To fetch the records of Name and Brand columns which are not present in both record-sets, the following SQL code can be used:

    SELECT Name, Brand FROM Product
    MINUS
    SELECT Name, Brand FROM Inventory
    ORDER BY Name;
    

    This will produce the following result:

    NameBrand
    AppleHarvest
    MangoLocalFarm
    OrangeDelicious
    OrangeHarvest