Set Operations: UNION and UNION ALL

Stacking instead of joining#

Joins add columns side by side. UNION adds rows top to bottom. It's what you want when two queries return the same shape from different sources.

SELECT name, email, 'customer' AS source FROM customers
UNION
SELECT name, email, 'supplier' AS source FROM suppliers;

Rules: each query must return the same number of columns, with compatible types, and the column names come from the first query.

UNION versus UNION ALL#

UNION removes duplicate rows. UNION ALL keeps everything.

query A: 1, 2, 3        UNION      ▶ 1, 2, 3, 4
query B: 3, 4           UNION ALL  ▶ 1, 2, 3, 3, 4

Removing duplicates means MySQL builds a temporary table and sorts or hashes every row. If you know the two sets can't overlap, or you want the duplicates, use UNION ALL. It's cheaper, and it's the one I reach for by default.

Ordering and limiting a union#

(SELECT id, name, price FROM products WHERE category = 'home' ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT id, name, price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 2)
ORDER BY price DESC;

Parenthesize each branch to give it its own ORDER BY ... LIMIT. The final ORDER BY applies to the combined result and can only use column names from the first query.

A practical use: combining activity logs#

SELECT customer_id, ordered_at AS happened_at, 'order' AS kind FROM orders
UNION ALL
SELECT customer_id, created_at, 'signup' FROM customers_events
ORDER BY happened_at DESC;

A single timeline from multiple tables is a standard real-world case.

INTERSECT and EXCEPT#

MySQL 8.0.31 and later support INTERSECT and EXCEPT:

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

On older servers, emulate them with JOIN or NOT EXISTS.

Common mistakes#

• Using UNION when UNION ALL is correct, and paying for needless deduplication.
• Mismatched column order. UNION matches by position, not name.
• Expecting the second query's aliases to apply.
• Mixing incompatible types and getting surprising implicit conversion.

Practice#

Produce one list of all city names found in customers and a hypothetical warehouses table, without duplicates. Then produce a combined activity feed of orders and customer signups, newest first, labelled by type. State which union form you used each time and why.