Set Operations & Data Comparison

Tutorial 7 of 11

Set operations combine the results of two or more SELECT statements. They answer questions like “which items appear in both lists?” or “which items are in list A but not list B?” —without writing complex joins. This tutorial uses a product inventory scenario with two warehouses.

1. Schema and seed data

Two warehouses, each with its own inventory table. Some products overlap, some are exclusive to one warehouse.

CREATE TABLE warehouse_east (
    product_id   INT PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity     INT NOT NULL
);

CREATE TABLE warehouse_west (
    product_id   INT PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity     INT NOT NULL
);

INSERT INTO warehouse_east VALUES
    (1, 'Widget A',  100),
    (2, 'Widget B',  200),
    (3, 'Gadget X',   50),
    (4, 'Gadget Y',   75),
    (5, 'Gizmo Z',   30);

INSERT INTO warehouse_west VALUES
    (2, 'Widget B',  150),
    (3, 'Gadget X',   80),
    (5, 'Gizmo Z',   60),
    (6, 'Doohickey', 120),
    (7, 'Thingamajig', 90);

5 products in each warehouse. Products 2, 3, and 5 appear in both. Products 1 and 4 are East-only; 6 and 7 are West-only.

2. UNION — all unique products

UNION combines two result sets and removes duplicates. This gives us a complete product catalog across both warehouses.

SELECT product_id, product_name
FROM warehouse_east
UNION
SELECT product_id, product_name
FROM warehouse_west
ORDER BY product_id;

3. UNION ALL — preserving duplicates

UNION ALL keeps all rows, including duplicates. This is useful when you want to see every inventory record, or when you know there are no duplicates and want to skip the deduplication cost.

SELECT product_name, quantity,
       'east' AS warehouse
FROM warehouse_east
UNION ALL
SELECT product_name, quantity,
       'west' AS warehouse
FROM warehouse_west
ORDER BY product_name, warehouse;

4. INTERSECT — products in both warehouses

INTERSECT returns only rows that appear in both result sets. This answers “which products do we stock in both locations?”

SELECT product_id, product_name
FROM warehouse_east
INTERSECT
SELECT product_id, product_name
FROM warehouse_west
ORDER BY product_id;

5. EXCEPT — products only in East

EXCEPT returns rows from the first query that do not appear in the second. This finds East-exclusive products.

SELECT product_id, product_name
FROM warehouse_east
EXCEPT
SELECT product_id, product_name
FROM warehouse_west
ORDER BY product_id;

6. EXCEPT reversed — products only in West

Swap the operands to find West-exclusive products. Order matters with EXCEPT—it is not symmetric.

SELECT product_id, product_name
FROM warehouse_west
EXCEPT
SELECT product_id, product_name
FROM warehouse_east
ORDER BY product_id;

7. Combined inventory with totals

Use UNION ALL inside a subquery, then aggregate to get total stock across both warehouses for each product.

SELECT product_name,
       SUM(quantity) AS total_stock,
       COUNT(*) AS warehouses
FROM (
    SELECT product_name, quantity
    FROM warehouse_east
    UNION ALL
    SELECT product_name, quantity
    FROM warehouse_west
) AS combined
GROUP BY product_name
ORDER BY total_stock DESC;

What you learned

In this tutorial you compared two warehouse inventories using all four set operations:

• UNION to merge result sets with automatic deduplication
• UNION ALL to merge without deduplication (faster, preserves all rows)
• INTERSECT to find rows common to both sets
• EXCEPT to find rows in one set but not the other
• Subquery + aggregate pattern to summarize combined data
• Tagging rows with a literal column ('east' AS warehouse) before combining