UNION and set operations

Set operators combine the results of two queries. A join puts columns side by side; a set operator stacks rows on top of each other, or compares two lists of rows. Interviewers ask "UNION or UNION ALL?" and "JOIN or UNION?" because the answers show whether you understand what a query returns. Testers also use set operators to compare data: "which ids are in the source but not in the target?" The examples use the company data.

UNION and UNION ALL

UNION stacks two results and removes duplicate rows (a duplicate is a row identical to another row). UNION ALL keeps every row. Here are the customers of delivered orders and of shipped orders, first without repeats and then with them:

SELECT customer FROM orders WHERE status = 'DELIVERED'
UNION
SELECT customer FROM orders WHERE status = 'SHIPPED';

customer
Acme Ltd
Bharat Traders
Cosmo Retail
Echo Stores
Fresh Mart

SELECT customer FROM orders WHERE status = 'DELIVERED'
UNION ALL
SELECT customer FROM orders WHERE status = 'SHIPPED';

customer
Acme Ltd
Acme Ltd
Bharat Traders
Echo Stores
Fresh Mart
Cosmo Retail
Cosmo Retail

Acme Ltd has two delivered orders and Cosmo Retail two shipped ones, so UNION ALL lists each twice. UNION removes repeats inside each query too, not only between the two. UNION ALL is usually faster because it skips the duplicate check, so use it when repeats cannot happen or are meaningful, for example when you count events from two log tables.

The rules

Every query must return the same number of columns. Columns are matched by position, not by name, and their types should be compatible (numbers with numbers, text with text). The result takes its column names from the first query. One ORDER BY at the very end sorts the whole result; without it, rows may come back in any order.

Because matching is by position, swapping two columns in one query mixes the data without any error. A constant column (a fixed text value, here 'employee' and 'customer') can record where each row came from:

SELECT name, 'employee' AS kind FROM employees WHERE dept_id = 3
UNION ALL
SELECT DISTINCT customer, 'customer' FROM orders WHERE employee_id = 7
ORDER BY kind, name;

name | kind
Acme Ltd | customer
Bharat Traders | customer
Cosmo Retail | customer
Arjun Das | employee
Kavya Iyer | employee

INTERSECT: rows in both

INTERSECT keeps rows that both queries return. Customers who have a delivered order and also an order in another state:

SELECT customer FROM orders WHERE status = 'DELIVERED'
INTERSECT
SELECT customer FROM orders WHERE status <> 'DELIVERED';

customer
Acme Ltd
Bharat Traders

EXCEPT: rows in the first only

EXCEPT keeps rows of the first query that the second does not return. Customers with orders but none delivered:

SELECT customer FROM orders
EXCEPT
SELECT customer FROM orders WHERE status = 'DELIVERED';

customer
Cosmo Retail
Delta Foods

This is the heart of many data comparisons. After a migration (moving data from an old table or system to a new one), SELECT id FROM old_table EXCEPT SELECT id FROM new_table lists rows that went missing, and the reverse lists rows that appeared from nowhere. Both should be empty.

Like UNION, INTERSECT and EXCEPT remove duplicates from their result. PostgreSQL and SQLite support both. MySQL added them in version 8.0.31, so older MySQL needs IN, EXISTS or a left join instead. Oracle has long used the name MINUS for EXCEPT.

Set operators or joins?

A join combines columns from related rows of two tables, matched by a condition, so rows get wider. A union combines the rows of two queries with the same shape, so the result gets longer. Many INTERSECT and EXCEPT questions can also be answered with EXISTS, NOT EXISTS or a left join, and that form also works in old MySQL versions:

SELECT DISTINCT o.customer FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders d
                  WHERE d.customer = o.customer AND d.status = 'DELIVERED');

customer
Cosmo Retail
Delta Foods

How to answer in an interview

For "UNION versus UNION ALL", say: UNION removes duplicate rows, UNION ALL keeps them and is faster because it skips that check. For "JOIN versus UNION", say: a join adds columns, a union adds rows. Then give one tester use, such as EXCEPT in both directions to compare two tables after a migration.

Common mistakes

• Using UNION where repeats matter, so counts come out too low.
• Listing columns in a different order in each query.
• Putting ORDER BY in the first query instead of at the end.
• Assuming INTERSECT and EXCEPT exist in every MySQL version.

Recap

UNION stacks rows without repeats and UNION ALL keeps every row. INTERSECT keeps rows found by both queries, and EXCEPT keeps rows found only by the first. Columns line up by position, and one ORDER BY at the end sorts the whole result.