EXCEPT
EXCEPT
Overview
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.
Syntax
The syntax for the EXCEPT
is as follows:
SELECT value1, value2, ... value_n
FROM table1
EXCEPT
SELECT value1, value2, ... value_n
FROM table2;
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.
Example
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:
CREATE TABLE vehicles (
vhc_id INT,
vhc_name TEXT
);
CREATE TABLE vehicles1 (
vhc_id INT,
vhc_name TEXT
);
INSERT INTO vehicles VALUES
(1, 'Truck'),
(2, 'Car'),
(3, 'Motorcycle');
INSERT INTO vehicles1 VALUES
(2, 'Car'),
(3, 'Bus'),
(4, 'Motorcycle');
Display the tables with the query below:
SELECT * FROM vehicles;
SELECT * FROM vehicles1;
vhc_id | vhc_name
--------+------------
1 | Truck
2 | Car
3 | Motorcycle
vhc_id | vhc_name
--------+------------
2 | Car
3 | Bus
4 | Motorcycle
Using the EXCEPT
to find employees present in 2021 but not in 2022:
SELECT vhc_name FROM vehicles
EXCEPT
SELECT vhc_name FROM vehicles1;
The result will include the names of employees who were present in 2021 but are not present in 2022:
vhc_name
----------
Truck
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.”
EXCEPT ALL
Overview
The EXCEPT ALL
allows you to find rows specific to the first SELECT
statement while preserving duplicate entries.
Syntax
The syntax for the EXCEPT ALL
is similar to EXCEPT
:
SELECT value1, value2, ... value_n
FROM table1
EXCEPT ALL
SELECT value1, value2, ... value_n
FROM table2;
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.
SELECT
queries must be compatible.Example #1
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.
CREATE TABLE marketplace1_transactions (
customer_id INT,
product_id INT,
amount FLOAT
);
CREATE TABLE marketplace2_transactions (
customer_id INT,
product_id INT,
amount FLOAT
);
INSERT INTO marketplace1_transactions VALUES
(101, 1, 100.00),
(102, 2, 150.00),
(103, 3, 200.00),
(104, 1, 120.00);
INSERT INTO marketplace2_transactions VALUES
(102, 3, 180.00),
(103, 2, 160.00),
(105, 4, 90.00),
(106, 1, 110.00);
Display the tables using the query below:
SELECT * FROM marketplace1_transactions;
SELECT * FROM marketplace2_transactions;
customer_id | product_id | amount
-------------+------------+--------
101 | 1 | 100
102 | 2 | 150
103 | 3 | 200
104 | 1 | 120
customer_id | product_id | amount
-------------+------------+--------
102 | 3 | 180
103 | 2 | 160
105 | 4 | 90
106 | 1 | 110
Using the EXCEPT ALL
to find customers who have purchased products from one marketplace but not from the other:
SELECT customer_id FROM marketplace1_transactions
EXCEPT ALL
SELECT customer_id FROM marketplace2_transactions;
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.
customer_id
-------------
104
101
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
).
Example #2
Let’s create two tables, left_array_values
and right_array_values
, to hold sets of values.
CREATE TABLE left_array_values (
value INT
);
CREATE TABLE right_array_values (
value INT
);
INSERT INTO left_array_values VALUES (1), (1), (3);
INSERT INTO right_array_values VALUES (1), (2);
View the contents of the two arrays before performing the comparison.
SELECT * FROM left_array_values;
SELECT * FROM right_array_values;
Upon execution, the tables will appear as follows:
value
-------
1
1
3
value
-------
1
2
We will now use the EXCEPT ALL
operation to compare the values within the arrays, focusing on unique elements while retaining duplicate entries.
SELECT value
FROM left_array_values
EXCEPT ALL
SELECT value
FROM right_array_values;
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.
value
-------
3
1