UNION, INTERSECT and EXCEPT

Set operators combine the results of two separate SELECT queries — both queries must return the same number of columns, with compatible types.

UNION — Combine Rows, Remove Duplicates

SELECT name FROM employees
UNION
SELECT name FROM contractors;
-- every distinct name across both tables

UNION ALL — Combine Rows, Keep Duplicates

Faster than UNION since it skips the work of de-duplicating — use it whenever you know there won't be (or don't care about) duplicates.

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

INTERSECT — Rows in Both Queries

SELECT email FROM employees
INTERSECT
SELECT email FROM newsletter_subscribers;
-- employees who are ALSO subscribed to the newsletter

EXCEPT — Rows in the First Query, Not the Second

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

The order matters — A EXCEPT B is not the same as B EXCEPT A.

Common Errors & Troubleshooting

• ERROR: each UNION query must have the same number of columns — both SELECTs must return the exact same column count; add a placeholder (like NULL AS extra_col) to the shorter query if needed.
• ERROR: UNION types text and integer cannot be matched — the corresponding columns' types must be compatible; cast one side explicitly (column::TEXT) if they genuinely represent the same kind of information stored differently.
• Using UNION when UNION ALL was intended, and eating a performance cost — UNION always de-duplicates the combined result, which requires an extra sort/comparison step; if you already know there are no duplicates (or don't care), UNION ALL is faster.

Key Takeaways

• All three operators require the two queries to return the same number of columns with compatible types.
• UNION deduplicates by default; UNION ALL keeps every row and is faster.
• EXCEPT is order-sensitive — it means "in the first result set, but not the second."