Set Operations

JOINs combine tables horizontally - they bring in new columns. Set operations go the other direction: they stack result sets vertically, adding rows. The three operators are UNION, INTERSECT, and EXCEPT. Each one answers a different kind of question about what is in two result sets relative to each other.

One requirement before any of this works: both queries must return the same number of columns with compatible types. Get that wrong and you get an error, not a result.

UNION

UNION stacks two result sets and removes duplicate rows:

SELECT name, email FROM customers
UNION
SELECT name, email FROM prospects;

If the same (name, email) pair appears in both tables, it shows up once in the result. That deduplication step has a cost - the database has to sort or hash the full result set to find duplicates.

UNION ALL keeps every row, including duplicates, and skips the deduplication:

SELECT product_id, quantity FROM warehouse_a
UNION ALL
SELECT product_id, quantity FROM warehouse_b;

UNION ALL is faster because it skips that work entirely. Use it when you know duplicates cannot exist, or when you want every row counted. Default to UNION ALL and reach for UNION only when deduplication is actually what you need.

INTERSECT

INTERSECT returns only rows that appear in both result sets:

SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

This gives you customers who placed at least one order in both years. Duplicates within each side are removed before the intersection runs, so the result contains distinct IDs.

EXCEPT

EXCEPT returns rows from the first query that do not appear in the second. It is the set subtraction operator:

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

This gives you customers who have never placed an order. Order matters here - flip the two queries and you get a completely different answer. That asymmetry trips people up. Think of it as subtraction: first set minus second set. Oracle named it MINUS for exactly that reason.

Summary of set operators

Column names and types

Column names in the final result come from the first query. Whatever you name the columns in the second query, those names are ignored. You will see this and wonder why your alias did not work - check which query is first.

Types must be compatible, but not identical. PostgreSQL will cast between things like integer and numeric automatically.

Sorting the combined result

You can sort the combined result, but ORDER BY can only appear once - at the very end, after all the set operations:

SELECT name, 'customer' AS source FROM customers
UNION ALL
SELECT name, 'prospect' AS source FROM prospects
ORDER BY name;

Adding ORDER BY inside one of the individual queries is an error. Sorting only makes sense on the final combined result.

When to use set operations vs other approaches

Set operations shine when both sides have the same shape and you want a vertical combination - not a join. For "rows in A but not in B," EXCEPT is the most readable option. Just know that NOT EXISTS and LEFT JOIN ... WHERE b.id IS NULL handle NULLs more predictably if that matters for your data. When the data is clean and the intent is clear, EXCEPT is the right call.