SQL Tutorial SQL Advanced SQL Database SQL References

SQL - EXCEPT Keyword



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

Syntax

The syntax for using EXCEPT keyword is given below:

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

Note: By default, EXCEPT clause only takes distinct values. To select duplicate values, EXCEPT 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 EXCEPT keyword: 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 keyword 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 keyword 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

❮ SQL Keywords