Set operations — UNION, INTERSECT, and EXCEPT

A JOIN combines columns from two tables side by side. A set operation combines rows from two queries stacked on top of each other — a genuinely different kind of "combine," for a genuinely different kind of question.

Intermediate

4 min read

The shape of question set operations answer

-- "Everyone who is either a customer OR a newsletter subscriber" —
-- these are two separate tables/queries, and the question is about
-- combining their ROWS, not their columns
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

A JOIN (from its own lesson) combines columns from two tables, matched by some relationship — one output row per matching pair, wider than either input. A set operation combines rows from two queries, stacked vertically — the output has the same columns as the inputs (which must match in number and roughly in type), but potentially more rows, one set of rows from each query. These solve genuinely different problems: JOIN answers "what goes together," set operations answer "what's in this group, that group, or both."

UNION: everything from either query, duplicates removed

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;
-- every email that appears in either table, each one listed exactly once

UNION combines the results of two queries and removes duplicates — an email appearing in both customers and newsletter_subscribers shows up only once in the combined result. This deduplication isn't free: the database has to compare every row against every other row to find duplicates, which costs real work. UNION ALL keeps every row from both queries, duplicates included, and skips that deduplication cost entirely — genuinely faster, and the right choice whenever duplicates either can't occur or don't matter for the question being asked.

INTERSECT: only rows present in both queries

SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;
-- only emails that are BOTH a customer AND a subscriber

INTERSECT returns only the rows that appear in both queries' results — "customers who are also subscribers," not everyone from either group. This is the set-operation equivalent of an INNER JOIN's "only matching rows" behavior, but for whole rows across two independent queries instead of matching on a specific join condition between two tables.

EXCEPT: rows in the first query, but not the second

SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;
-- customers who are NOT subscribed to the newsletter

EXCEPT (called MINUS in some databases, like Oracle) returns rows from the first query that don't appear in the second — order matters here, unlike UNION/INTERSECT: A EXCEPT B ("customers not subscribed") is a genuinely different question than B EXCEPT A ("subscribers who aren't customers"). This is the practical way to answer "who's missing from the second group" directly in SQL, without an anti-join pattern (WHERE NOT EXISTS (...) or LEFT JOIN ... WHERE right_side IS NULL) that expresses the same idea more verbosely.

The one hard requirement: matching columns

SELECT email FROM customers
UNION
SELECT id FROM newsletter_subscribers;   -- runs, but silently combines unrelated columns —
                                            -- both must at least be compatible types

Every query combined with a set operation must return the same number of columns, in compatible types, column by column — the database doesn't check that the columns mean the same thing, only that they're structurally compatible enough to stack. SELECT email and SELECT id would both run without error if id happens to be a text-compatible type, silently producing a nonsensical combined result — this is a real, easy mistake, since nothing about the syntax prevents combining columns that don't actually represent the same kind of thing.

Set operations vs. JOIN vs. subquery: picking the right tool

-- JOIN: I want columns from BOTH tables together, per matching row
SELECT customers.name, orders.amount FROM customers JOIN orders ON ...;
 
-- Subquery/IN: I want rows from ONE table, filtered by a condition from another
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);
 
-- Set operation: I want ROWS combined from two SEPARATE, similarly-shaped queries
SELECT email FROM customers UNION SELECT email FROM subscribers;

The tell for a set operation specifically: the question is about combining two lists of the same kind of thing (two lists of emails, two lists of product IDs) rather than combining related data from different tables, or filtering one table using a value computed from another. Reaching for a JOIN when the actual question is "which emails appear in either list" produces something structurally wrong — there's no meaningful column-level relationship to join on, because the question was never about relating two tables, it was about combining two sets.