SQL Tutorial SQL Advanced SQL Database SQL References

SQL - EXCEPT Clause



The SQL EXCEPT 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 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.
SQL EXCEPT
Note: The EXCEPT clause is not supported in all SQL databases. It can be used in databases such as SQL Server, PostgreSQL, and SQLite. For databases such as Oracle, MySQL and MariaDB uses the MINUS clause to perform this type of query.

Syntax

The syntax for using EXCEPT clause is given below:

SELECT column1, column2, ... FROM table1
EXCEPT
SELECT column1, column2, ... FROM table2

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 SQL code 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 SQL code 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 SQL code 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

5