MySQL – UNION and UNION ALL | Love Me Some Coding

A join puts tables side by side, adding columns. UNION stacks result sets on top of each other, adding rows. Different tool, different question: use a join to bring related information together, and a UNION to treat two sets of rows as one list.

Stacking two queries

The pizza schema stores a contact address in two different places — registered accounts have app_user.email, guest orders have customer_order.guest_email. One mailing list out of both:

SELECT u.email AS contact, 'account' AS source FROM app_user u
UNION ALL
SELECT o.guest_email, 'guest' FROM customer_order o WHERE o.guest_email IS NOT NULL
ORDER  BY source, contact
LIMIT  6;

+-----------------------+---------+
| contact               | source  |
+-----------------------+---------+
| admin@pizza.test      | account |
| customer@pizza.test   | account |
| abandoned@example.com | guest   |
| guest1@example.com    | guest   |
| guest2@example.com    | guest   |
| guest3@example.com    | guest   |
+-----------------------+---------+

The literal 'account' / 'guest' column is a useful habit: once rows from different sources are mixed, you usually want to know which branch each came from.

The rules

Every branch needs the same number of columns.
Corresponding columns need compatible types. MySQL will coerce where it can, so a mismatch often produces bad data rather than an error.
Column names come from the first branch. The alias on the second one is ignored, which is why the second query above does not bother naming its columns.

UNION vs UNION ALL

This is the part that matters. Plain UNION removes duplicate rows; UNION ALL keeps everything. Stacking the 18 orders' statuses onto themselves shows the difference:

SELECT COUNT(*) AS with_union_all FROM (
  SELECT status FROM customer_order UNION ALL SELECT status FROM customer_order) x;

+----------------+
| with_union_all |
+----------------+
|             36 |
+----------------+

SELECT COUNT(*) AS with_union FROM (
  SELECT status FROM customer_order UNION SELECT status FROM customer_order) x;

+------------+
| with_union |
+------------+
|          5 |
+------------+

36 rows collapse to 5 — the five distinct statuses. Deduplication is a whole-row DISTINCT, so it has to sort or hash everything both branches produced.

Default to UNION ALL. Reach for plain UNION only when duplicates are genuinely possible and genuinely unwanted. Writing UNION out of habit buys a sort you did not need — and, worse, can silently delete real rows: two different customers who happen to share a name and a total are one row after deduplication.

Where ORDER BY and LIMIT go

A trailing ORDER BY applies to the combined result, not to the last branch — which is what you almost always want, and also why it can only reference the column names from the first branch. To sort or limit a branch on its own, parenthesise it:

(SELECT id, total FROM customer_order WHERE order_type = 'DELIVERY' ORDER BY total DESC, id LIMIT 2)
UNION ALL
(SELECT id, total FROM customer_order WHERE order_type = 'CARRYOUT' ORDER BY total DESC, id LIMIT 2);

That is the "top 2 of each kind" shape, and it is one of the things UNION ALL does better than anything else short of a window function.

INTERSECT and EXCEPT

MySQL 8.0.31 added the other two set operators. INTERSECT keeps rows present in both branches:

SELECT status FROM customer_order WHERE order_type = 'DELIVERY'
INTERSECT
SELECT status FROM customer_order WHERE order_type = 'CARRYOUT'
ORDER  BY status;

+-----------+
| status    |
+-----------+
| CANCELLED |
| COMPLETED |
+-----------+

EXCEPT keeps rows in the first branch that are not in the second:

SELECT status FROM customer_order WHERE order_type = 'DELIVERY'
EXCEPT
SELECT status FROM customer_order WHERE order_type = 'CARRYOUT'
ORDER  BY status;

+-----------+
| status    |
+-----------+
| PAID      |
| PREPARING |
+-----------+

Both deduplicate like UNION does. If you are on an older MySQL 8, the equivalents are a join for INTERSECT and a LEFT JOIN ... IS NULL anti-join for EXCEPT.

When a UNION is the wrong answer

Reaching for UNION to produce several counts over the same table is a common habit worth losing:

-- three passes over the table
SELECT 'completed' AS bucket, COUNT(*) FROM customer_order WHERE status = 'COMPLETED'
UNION ALL
SELECT 'cancelled', COUNT(*) FROM customer_order WHERE status = 'CANCELLED'
UNION ALL
SELECT 'paid', COUNT(*) FROM customer_order WHERE status = 'PAID';

-- one pass, one row, and it reads better
SELECT SUM(status = 'COMPLETED') AS completed,
       SUM(status = 'CANCELLED') AS cancelled,
       SUM(status = 'PAID')      AS paid
FROM   customer_order;

That second form is conditional aggregation — see IF and CASE. One scan instead of three, and it gives you a row of columns rather than a column of rows, which is usually the shape a report wants anyway.

The other place to stop and think: if you find yourself unioning several tables that hold the same kind of thing, the tables probably want to be one table with a type column — which is exactly what the pizza schema does with product, keeping pizzas and drinks together and separating them by type.

What to remember

UNION adds rows; a join adds columns.
Same column count, compatible types, names from the first branch.
UNION ALL by default — plain UNION costs a sort and can delete real rows.
A trailing ORDER BY applies to the whole result; parenthesise a branch to sort it alone.
Several counts over one table want conditional aggregation, not a union.