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.

A Real Business Scenario: Reconciling Lists from Different Systems

Set operations are especially useful when the business task is "compare these two result sets" rather than "join these tables".

Examples:

• emails present in both the CRM export and the customer table
• SKUs in the new feed but not in the existing catalog
• users eligible under two separate selection rules

That is the right mental model for UNION, INTERSECT, and EXCEPT: they are for row-set comparison and combination, not for fetching extra columns from related tables.

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
('[email protected]', 'Alice'),
('[email protected]', 'Bob'),
('[email protected]', 'Charlie');

INSERT INTO customers (email, status) VALUES
('[email protected]', 'Active'),
('[email protected]', 'Active'),
('[email protected]', '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.

A Common Mistake: Using UNION by Default

Many people write UNION when they really mean UNION ALL.

That matters because UNION forces duplicate elimination, which can add sorting or hashing work and silently change row counts.

If your intent is "append both datasets and keep everything", UNION ALL is the more honest query shape.

Use UNION only when duplicate removal is part of the business requirement, not just because it feels safer.

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: [email protected], [email protected]

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: [email protected]

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.

Boundary and Performance Notes

Set operations are concise, but they still deserve the same discipline as joins and subqueries.

• UNION / INTERSECT / EXCEPT usually perform duplicate elimination, which costs more than their ALL variants.
• The meaning of equality for duplicate removal depends on the full projected row, not just one business key.
• Pushing filters down into each branch before the set operation often reduces work substantially.
• If you need provenance, add a source label column explicitly, especially with UNION ALL.

The cleanest set-operation queries make the intended row shape obvious before the branches are combined.

When NOT to Use Set Operations

Do not use set operations when the real job is relational joining.

Examples:

• you need customer attributes from another table, which calls for a join
• you need to compare existence row by row while preserving one side's columns, which may be clearer with EXISTS
• you need anti-join behavior in a dialect that lacks EXCEPT, where NOT EXISTS may be more portable

Set operations are great when the output of each branch is already in the same shape and you are combining or comparing those finished row sets.

Official References

• PostgreSQL combining queries documentation for UNION, INTERSECT, and EXCEPT semantics.
• SQLite compound SELECT documentation for SQLite rules around compound queries and duplicate handling.
• Oracle set operators documentation for another major-engine reference, including MINUS.

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!

Related Articles

• SQL Subqueries Explained: Queries Within Queries for another way to express row filtering and comparison logic when one query depends on another.
• Mastering SQL Joins: An Interactive Guide for the cases where you need to combine columns from related tables instead of merging row sets.
• Handling NULLs in SQL: The Ultimate Guide for the edge cases that affect EXCEPT, NOT IN, and other comparison-heavy query patterns.