Set Operations

Most SQL queries work with a single table or join several tables together. But sometimes the question you want to answer is inherently about combining or comparing two separate result sets: "Give me all the rows from query A plus all the rows from query B", or "Give me only the rows that appear in both results", or "Give me rows from query A that are missing from query B." This is exactly what set operations do.

SQL gives you three operators for this: UNION, INTERSECT, and EXCEPT. They treat each SELECT statement as a set of rows and perform the corresponding mathematical set operation on the results. Knowing when to reach for these operators — instead of a complex join or nested subquery — leads to cleaner, more expressive queries.

The Rules

Before diving in, every set operation has two hard requirements:

1. Same number of columns — both SELECT statements must return the same number of columns.
2. Compatible types — corresponding columns must have compatible data types. A text column cannot be combined with a numeric column.

The column names in the final result come from the first SELECT statement. Any ORDER BY clause applies to the combined result and must go at the very end, after all the branches.

UNION and UNION ALL

UNION merges two result sets into one, automatically removing duplicate rows. UNION ALL does the same merge but keeps every row, including duplicates. If you know there are no duplicates — or you deliberately want to preserve them — UNION ALL is faster because it skips the deduplication step.

-- Combine two separate sales channels into a single report
CREATE TABLE online_orders (
    order_id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product  TEXT NOT NULL,
    amount   REAL NOT NULL
);

CREATE TABLE store_orders (
    order_id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product  TEXT NOT NULL,
    amount   REAL NOT NULL
);

INSERT INTO online_orders VALUES
    (1, 'Alice', 'Keyboard', 89.99),
    (2, 'Bob',   'Monitor',  349.00),
    (3, 'Carol', 'Keyboard', 89.99);

INSERT INTO store_orders VALUES
    (1, 'David', 'Mouse',   29.99),
    (2, 'Alice', 'Headset', 79.00),
    (3, 'Bob',   'Monitor', 349.00);

-- UNION removes exact duplicate rows across both sets
SELECT customer, product, amount, 'online' AS channel
FROM online_orders
UNION
SELECT customer, product, amount, 'store'
FROM store_orders
ORDER BY customer;

Notice that every row here carries a channel column, so no two rows are truly identical even if the same customer bought the same product in both places. Deduplication only kicks in when every column in a row matches exactly. Remove the channel column and Bob's Monitor row would collapse into one — try it.

INTERSECT

INTERSECT returns only the rows that appear in both result sets. Think of it as finding the overlap — the products that exist in both catalogs, the customers who show up on two different lists.

-- Find products sold through BOTH the online store and the physical store
CREATE TABLE online_products (name TEXT PRIMARY KEY, price REAL);
CREATE TABLE store_products  (name TEXT PRIMARY KEY, price REAL);

INSERT INTO online_products VALUES
    ('Keyboard', 89.99),
    ('Monitor',  349.00),
    ('Webcam',   59.99),
    ('Headset',  79.00);

INSERT INTO store_products VALUES
    ('Monitor',  349.00),
    ('Headset',  79.00),
    ('Desk Lamp', 34.99),
    ('Mouse',    29.99);

-- Only products carried by both channels
SELECT name, price FROM online_products
INTERSECT
SELECT name, price FROM store_products;

INTERSECT is a readable alternative to a subquery using IN. The equivalent WHERE name IN (SELECT name FROM store_products) works, but the INTERSECT version makes the set-theory intent immediately visible to anyone reading the query later.

EXCEPT

EXCEPT returns rows that are in the first result set but not in the second. It is the set difference: "everything in A that is not in B." Some databases call this MINUS instead of EXCEPT — Oracle and older DB2 both use MINUS — but the behavior is identical.

-- Find products available online but NOT stocked in the physical store
CREATE TABLE online_items (name TEXT, category TEXT);
CREATE TABLE store_items  (name TEXT, category TEXT);

INSERT INTO online_items VALUES
    ('Keyboard',  'Peripherals'),
    ('Monitor',   'Displays'),
    ('Webcam',    'Peripherals'),
    ('Headset',   'Audio'),
    ('USB Hub',   'Accessories');

INSERT INTO store_items VALUES
    ('Monitor',  'Displays'),
    ('Headset',  'Audio'),
    ('Desk Lamp','Lighting'),
    ('Mouse',    'Peripherals');

-- Items the online store carries that the physical store does not
SELECT name, category FROM online_items
EXCEPT
SELECT name, category FROM store_items
ORDER BY name;

The order of the two branches matters with EXCEPT. Swap them and you get the opposite result: items the store stocks that are not available online. This asymmetry is what distinguishes EXCEPT from INTERSECT, which is symmetric — swapping the branches gives the same answer.

Try It Yourself

A company tracks newsletter subscribers and paying customers in separate tables. Use set operations to answer three questions in turn: who is either a subscriber or a customer (or both), who is both a subscriber and a paying customer, and who subscribed to the newsletter but has never made a purchase.

CREATE TABLE subscribers (
    email TEXT PRIMARY KEY,
    name  TEXT NOT NULL
);

CREATE TABLE customers (
    email TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    spent REAL NOT NULL
);

INSERT INTO subscribers VALUES
    ('alice@example.com',  'Alice'),
    ('bob@example.com',    'Bob'),
    ('carol@example.com',  'Carol'),
    ('dan@example.com',    'Dan'),
    ('eva@example.com',    'Eva');

INSERT INTO customers VALUES
    ('bob@example.com',   'Bob',   250.00),
    ('carol@example.com', 'Carol',  89.50),
    ('frank@example.com', 'Frank', 410.00),
    ('eva@example.com',   'Eva',    74.99);

-- 1. Everyone who is a subscriber OR a customer (no duplicates)
SELECT email, name FROM subscribers
UNION
SELECT email, name FROM customers;

-- 2. People who are BOTH a subscriber AND a customer
-- SELECT email, name FROM subscribers
-- INTERSECT
-- SELECT email, name FROM customers;

-- 3. Subscribers who have NEVER made a purchase
-- SELECT email, name FROM subscribers
-- EXCEPT
-- SELECT email, name FROM customers;

Uncomment each block in turn and run it to see how the results change. Then add a new row to customers with an email that already exists in subscribers — observe how UNION collapses it and how EXCEPT adjusts the missing-purchaser list automatically.

Key Takeaways

• UNION combines two result sets and removes duplicate rows; UNION ALL keeps all rows including duplicates — prefer UNION ALL when duplicates are meaningful or impossible, since it avoids a costly deduplication pass.
• INTERSECT returns only the rows that exist in both result sets and is the most readable way to express "find the overlap between two queries."
• EXCEPT returns rows from the first result set that do not appear in the second — order matters; swapping the branches produces the inverse result.
• All set operations require the same number of columns with compatible types; column names in the output always come from the first SELECT branch.
• A single ORDER BY clause goes at the very end of the entire combined statement and sorts the final merged result.
• Set operations can replace correlated subqueries and IN / NOT IN patterns in many common scenarios, often producing queries that are easier to read and reason about.

Pro Tip: When you need to understand why rows appear in one set but not another, add a label column to each branch — 'online' AS source — and switch from EXCEPT to UNION ALL. Instead of just seeing what is missing, you get a combined view of both sets side by side, which makes discrepancies immediately visible and much easier to debug.

Next Steps

With set operations in your toolkit, the next lesson dives into data types and casting — understanding how SQL stores values and how to convert between types safely to avoid subtle bugs.