MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB - EXCEPT Clause



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

While using EXCEPT 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.
MariaDB EXCEPT

Syntax

The syntax for using EXCEPT clause in MariaDB is given below:

SELECT column1, column2, ... FROM table1
EXCEPT [ALL | DISTINCT]
SELECT column1, column2, ... FROM table2

Note: By default, EXCEPT clause only takes distinct values. To select duplicate values, EXCEPT ALL clause can be used. To force this clause to select distinct values EXCEPT DISTINCT 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 EXCEPT clause: To get the records of Name column which are not present in both record-sets, the following query can be used:

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

    This will produce the result as shown below:

    Name
    Mango
  • Using EXCEPT 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 query can be used:

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

    This will produce the following result:

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

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

    This will produce the following result:

    NameBrand
    AppleHarvest
    MangoLocalFarm
    OrangeDelicious
    OrangeHarvest