Cloud

EXCEPT

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

SELECT value1, value2, ... value_n
FROM table1
EXCEPT
SELECT value1, value2, ... value_n
FROM table2;

Where:

  • value1, value2, …​ value_n: The columns to retrieve.

  • table1, table2: The tables to retrieve records from.

Examples

Suppose you have two tables: vehicles_2021 and vehicles_2022. The goal is to find the vehicle that was present in 2021 but is not present in 2022:

CREATE TABLE vehicles_2021 (
    vhc_id INT,
    vhc_name TEXT
);

CREATE TABLE vehicles_2022 (
    vhc_id INT,
    vhc_name TEXT
);

INSERT INTO vehicles_2021 VALUES
(1, 'Truck'),
(2, 'Car'),
(3, 'Motorcycle');

INSERT INTO vehicles_2022 VALUES
(2, 'Car'),
(3, 'Bus'),
(4, 'Motorcycle');

View the tables:

SELECT * FROM vehicles_2021;
SELECT * FROM vehicles_2022;
vhc_id |  vhc_name
--------+------------
      1 | Truck
      2 | Car
      3 | Motorcycle

 vhc_id |  vhc_name
--------+------------
      2 | Car
      3 | Bus
      4 | Motorcycle

Use EXCEPT to find vehicle names present in the first table but not in the second:

SELECT vhc_name FROM vehicles_2021
EXCEPT
SELECT vhc_name FROM vehicles_2022;

The query returns vehicle names that exist in vehicles_2021 but not in vehicles_2022:

vhc_name
----------
 Truck

Only Truck is present in the first table but not the second.

EXCEPT ALL

EXCEPT ALL finds rows specific to the first SELECT statement while preserving duplicate entries.

Syntax

SELECT value1, value2, ... value_n
FROM table1
EXCEPT ALL
SELECT value1, value2, ... value_n
FROM table2;

Where:

  • value1, value2, …​ value_n: The columns to retrieve.

  • table1, table2: The tables to retrieve records from.

Customers in only one marketplace

To identify customers who have purchased products from one marketplace but not from another, create the tables and populate 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);

View the tables:

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

Use 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;

The query returns the customer_id values that appear in the first marketplace but not in the second:

customer_id
-------------
         104
         101

Compare arrays with duplicates

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 tables before performing the comparison:

SELECT * FROM left_array_values;
SELECT * FROM right_array_values;

The tables contain:

value
-------
     1
     1
     3

 value
-------
     1
     2

Use EXCEPT ALL to compare the values, focusing on unique elements while retaining duplicate entries:

SELECT value
FROM left_array_values
EXCEPT ALL
SELECT value
FROM right_array_values;

EXCEPT ALL compares elements pairwise, so both 1 and 3 appear in the final result:

value
-------
     3
     1