EXCEPT
The EXCEPT combines the result sets of two or more tables and retrieves rows specific to the first SELECT statement but not present in the subsequent ones.
The syntax for the EXCEPT is as follows:
The parameters from the syntax are explained below:
- value1, value2, ... value_n: The columns you want to retrieve.
- table1, table2: The tables from which you wish to retrieve records.
Let's assume you have two tables: vehicles and vehicles1. You want to find the vehicle which was present in 2021 but is not present in 2022:
Display the tables with the query below:
Using the EXCEPT to find employees present in 2021 but not in 2022:
The result will include the names of employees who were present in 2021 but are not present in 2022:
From the diagram below, we learn that the result is a list of vehicle names present in the first table (vehicles) but not found in the second table (vehicles1). In this case, the result is the vehicle name "Truck."

The EXCEPT ALL allows you to find rows specific to the first SELECT statement while preserving duplicate entries.
The syntax for the EXCEPT ALL is similar to EXCEPT:
The parameters from the syntax are explained below:
- value1, value2, ... value_n: The columns you want to retrieve.
- table1, table2: The tables from which you wish to retrieve records.
Note:
The data types of corresponding columns in the SELECT queries must be compatible.
You aim to identify customers who have bought products from one marketplace but have not purchased from another. Start by creating the tables and populating them with relevant data.
Display the tables using the query below:
Using the EXCEPT ALL to find customers who have purchased products from one marketplace but not from the other:
This result will show a customer_id who has only transacted in the first marketplace and has not engaged in any corresponding transactions in the second marketplace.
The diagram below shows a list of customer-product pairs found in the first marketplace (marketplace1_transactions) but missing in the second marketplace (marketplace2_transactions).

Letβs create two tables, left_array_values and right_array_values, to hold sets of values.
View the contents of the two arrays before performing the comparison.
Upon execution, the tables will appear as follows:
We will now use the EXCEPT ALL operation to compare the values within the arrays, focusing on unique elements while retaining duplicate entries.
The EXCEPT ALL operation processes each element individually from both inputs at a time. The comparison occurs element-wise, leading to the inclusion of both 1 and 3 in the final result.
ο»Ώ