Set operations

Joins glue tables together side by side — more columns per row. Set operations stack results top to bottom — more rows, same columns. When you have two queries that produce the same shape of result and you want them in one list, you reach for UNION, INTERSECT, or EXCEPT.

The seed has two contact lists: newsletter_subscribers and webinar_attendees. Some people are on both, and one name is duplicated within a list — handy for seeing how deduplication works.

UNION: combine and deduplicate

UNION runs two queries and returns every row from either, with duplicates removed.

SELECT email, name FROM newsletter_subscribers
UNION
SELECT email, name FROM webinar_attendees
ORDER BY email;

Grace and Linus were on both lists, but each appears once. The duplicate Linus inside the newsletter list is also collapsed. UNION always returns a distinct set — that dedup is its defining behavior.

The only rule: both sides must have the same number of columns, in compatible types. The column names come from the first query.

UNION ALL: keep every row

Deduplication isn't free — Postgres has to compare rows to find duplicates. If you know there are none, or you want to keep them, UNION ALL skips that work and concatenates the two results as-is.

SELECT email, name FROM newsletter_subscribers
UNION ALL
SELECT email, name FROM webinar_attendees
ORDER BY email;

Now Grace and Linus appear twice (once per list), and the duplicate Linus shows a third time. UNION ALL is faster and is usually what you want when the inputs are already disjoint — e.g. stitching together this month's and last month's events.

Rule of thumb: reach for UNION ALL by default and only use plain UNION when you actually need the deduplication. Paying for a sort you don't need is a common, quiet performance bug.

INTERSECT: rows in both

INTERSECT keeps only the rows that appear in both queries.