BrevFeed
Sign in

19. Set operations: UNION, INTERSECT, EXCEPT

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

A different kind of combining

Every join so far combines tables side by side — matching rows, producing wider results with columns from both sides. Set operations combine query results vertically — stacking or comparing two result sets with the same shape (same number of columns, compatible types), producing a result with the same width as either input, just a different set of rows.

All three set operations require both queries to return the same number of columns, with compatible (implicitly castable) types in the same positions — column names don't need to match; the final result takes its column names from the first query.

UNION and UNION ALL

UNION combines two result sets and removes duplicate rows (a full row must match exactly to count as a duplicate — same rule as DISTINCT from Module 1). UNION ALL combines them and keeps every row, duplicates included.

-- Every distinct city name that's either a customer's city OR appears in
-- the address book at all — duplicates collapsed.
SELECT city FROM city WHERE country_id = 1
UNION
SELECT city FROM city WHERE country_id = 2;

Default to UNION ALL unless you specifically need deduplication. UNION's duplicate removal isn't free — like DISTINCT, it requires comparing rows (typically a sort or hash), real cost on a large result set. If you already know the two sources can't overlap (common — e.g. combining "active customers" and "inactive customers," which are mutually exclusive by construction), UNION ALL gives the identical result faster, with no wasted work:

-- Both branches are mutually exclusive by the WHERE conditions — UNION ALL
-- is strictly correct here AND faster than UNION, since there's nothing
-- to deduplicate.
SELECT customer_id, 'active' AS status FROM customer WHERE active = 1
UNION ALL
SELECT customer_id, 'inactive' AS status FROM customer WHERE active = 0;

INTERSECT

Returns only rows that appear in both result sets:

-- Customers who exist in BOTH a "rented an R-rated film" list AND a
-- "rented a G-rated film" list — i.e. customers who've rented both kinds.
SELECT customer_id FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id WHERE f.rating = 'R'
INTERSECT
SELECT customer_id FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id WHERE f.rating = 'G';

This is a genuinely different question than a JOIN or WHERE ... AND could answer directly — you're not filtering rows of one query, you're comparing the membership of two independent result sets. INTERSECT is equivalent to (and often rewritten by the planner into) a semi-join pattern using EXISTS, which you'll meet properly in Module 6.

EXCEPT

Returns rows in the first result set that do not appear in the second — set subtraction, order matters:

-- Customers who've rented a G-rated film but NEVER an R-rated one.
SELECT customer_id FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id WHERE f.rating = 'G'
EXCEPT
SELECT customer_id FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id WHERE f.rating = 'R';

(MySQL historically calls this MINUS-adjacent territory differently — Oracle uses MINUS as the keyword for exactly this operation; Postgres and the SQL standard both use EXCEPT. Filed for recognition, not something to worry about while writing Postgres.)

Set operations vs. JOIN — when to reach for which

A rule of thumb: if you're combining rows from the same conceptual entity under different conditions (union), or comparing membership between two groups (intersect/except), reach for set operations. If you're pulling related-but-different columns together side by side (a customer's name next to their rental date), that's a JOIN. The two are not interchangeable — INTERSECT on customer IDs and JOIN on customer IDs answer genuinely different questions, and it's worth pausing to identify which one you actually mean before writing either.

Check yourself

  1. What has to be true about two queries' result shapes before you can UNION them?
  2. Why is UNION ALL the better default when you already know the two sources can't overlap?
  3. Rewrite "customers who've rented an R film but never a G film" using EXCEPT, and explain in one sentence why order matters for EXCEPT but not for UNION or INTERSECT.
← Previous Next →