Mastering SQL Set Operations: UNION, INTERSECT, and EXCEPT

When working with data, you often need to combine results from multiple queries. While JOINs are great for combining columns from related tables, Set Operations are designed to combine rows from different queries.

Think of it like Venn diagrams. You have two circles (datasets), and you want to find everything in both, everything in just one, or the overlap.

In this guide, we'll master the four core set operations:

1. UNION: Combines results and removes duplicates.
2. UNION ALL: Combines results and keeps duplicates.
3. INTERSECT: Finds rows that exist in both results.
4. EXCEPT: Finds rows in the first result that aren't in the second.

The Playground Data

Let's imagine we have two lists of email addresses: one from our "Newsletter Subscribers" and one from our "Customer List". Some people might be on both lists.

CREATE TABLE newsletter_subs (
    id SERIAL PRIMARY KEY,
    email TEXT,
    name TEXT
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT,
    status TEXT
);

INSERT INTO newsletter_subs (email, name) VALUES
('alice@example.com', 'Alice'),
('bob@example.com', 'Bob'),
('charlie@example.com', 'Charlie');

INSERT INTO customers (email, status) VALUES
('bob@example.com', 'Active'),
('david@example.com', 'Active'),
('alice@example.com', 'Inactive');

1. UNION vs. UNION ALL

Use UNION when you need a distinct list of items from both sources. Use UNION ALL when you want to just append one list to the other, duplicates and all.

When to use which?

• UNION: Slower because the database has to do extra work to remove duplicates.
• UNION ALL: Faster. Use it when you know there are no duplicates.

2. INTERSECT

INTERSECT returns only the rows that appear in both result sets. It's like the overlapping part of a Venn diagram.

If we want to find users who are both newsletter subscribers and customers:

SELECT email FROM newsletter_subs
INTERSECT
SELECT email FROM customers;
-- Result: alice@example.com, bob@example.com

3. EXCEPT

EXCEPT (called MINUS in Oracle) returns rows from the first query that are not present in the second query. Order matters here!

If we want to find newsletter subscribers who are NOT customers yet:

SELECT email FROM newsletter_subs
EXCEPT
SELECT email FROM customers;
-- Result: charlie@example.com

Visualizing Logic with Mermaid

Here is how the logic flows for EXCEPT:

Rules of the Road

To use set operations, your queries must follow these rules:

1. Same number of columns: Both queries must return the same number of columns.
2. Compatible data types: The corresponding columns must have compatible data types.

Summary

Operation	Description	Duplicates?
UNION	Combines rows	Removed
UNION ALL	Combines rows	Kept
INTERSECT	Returns common rows	Removed
EXCEPT	Returns difference	Removed

Mastering these operations gives you powerful tools to compare and merge datasets without complex join logic!