UNION and set operations

Why it matters

Joins put tables side by side: they add columns. Set operations stack results on top of each other, or compare them row by row. Analysts use them constantly: combining this year's and last year's sales tables, building one list of contacts from several systems, finding people on one list but not another, or running several checks and returning all the answers in one result.

The four operators

Take two queries, A and B, that return the same columns.

Oracle traditionally calls EXCEPT MINUS (Oracle 21c and later accept both). SQLite, PostgreSQL and SQL Server support all four; MySQL added INTERSECT and EXCEPT in version 8.0.31.

The rules

1. Both queries must return the same number of columns.
2. Columns are matched by position, not by name, and should have compatible types.
3. The output column names come from the first query.
4. ORDER BY goes once, at the very end, and sorts the combined result.

Worked examples

Two tables of email addresses: newsletter (people who signed up) and orders (people who bought).

Everyone we can contact (either list, each email once):

SELECT email FROM newsletter
UNION
SELECT email FROM orders;
-- ana, ben, chen, dee, gus

Subscribers who are also customers:

SELECT email FROM newsletter
INTERSECT
SELECT email FROM orders;
-- ana, chen

Subscribers who have never bought (a target list for a first-order discount):

SELECT email FROM newsletter
EXCEPT
SELECT email FROM orders;
-- ben, dee

Order matters for EXCEPT: orders EXCEPT newsletter gives gus, a buyer who never subscribed.

UNION vs UNION ALL

UNION has to sort or hash every row to remove duplicates, which costs time on big tables, and it can silently remove rows you meant to keep. If two stores each sold a 20.00 coffee on the same day, UNION would collapse those into one row and your revenue would be wrong.

SELECT 'north' AS store, sale_date, amount FROM north_sales
UNION ALL
SELECT 'south', sale_date, amount FROM south_sales;

Rule of thumb: use UNION ALL when combining data (stacking partitions, years, stores), and UNION only when you specifically want a distinct list. Adding a literal "source" column like 'north' above is a good habit: it records where each row came from and also stops identical rows from different sources looking like duplicates.

Set operations as a report builder

Because UNION ALL stacks rows, you can glue several one-row summaries into a tidy table:

SELECT 'orders' AS metric, COUNT(*) AS value FROM orders
UNION ALL
SELECT 'customers', COUNT(*) FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products;

You will use exactly this pattern for data quality checks later in the course.

Set operations vs joins

EXCEPT and NOT EXISTS can answer the same "in A but not in B" question. EXCEPT compares whole rows and returns only the selected columns, so it is short and clear for lists of keys. NOT EXISTS or a LEFT JOIN ... WHERE b.id IS NULL is better when you want other columns from A in the output, or when the match is on some columns but not all. Also note that set operations treat two NULLs as equal (so NULL rows are de-duplicated), while = in a join never matches NULL.

Common mistakes

• Different column counts (SELECT email, name ... UNION SELECT email ...): an error.
• Columns in a different order in each query. The query runs, but city ends up stacked under name. Always list columns explicitly, never SELECT *.
• ORDER BY inside each part. Put one ORDER BY at the end. (To sort or limit a single part, wrap it in a subquery or CTE.)
• Using UNION and losing legitimate duplicates in sums and counts.
• Case and whitespace differences. 'Ana@Example.com' and 'ana@example.com' are different rows. Clean with LOWER(TRIM(email)) in both queries first.

Quick recap

• UNION / UNION ALL stack results; INTERSECT keeps the overlap; EXCEPT keeps what is only in the first.
• Same number of columns, matched by position; names come from the first query.
• Default to UNION ALL for combining data.
• One ORDER BY, at the end.

Try it

Open the Do tab and build the "subscribed but never ordered" list with a set operator.