UNION and UNION ALL
Stack two or more result sets into one, and choose between the de-duplicating UNION and the cheaper UNION ALL.
SELECT ... UNION [ALL | DISTINCT] SELECT ... [ORDER BY ...] [LIMIT ...]UNION takes the results of two or more query blocks and stacks them into a single result set. Columns are matched by position, not by name, so the first column of the second block lands under the first column of the first block whatever either is called. It exists for the case where the rows you want live in more than one place: a live table and its archive, two tables of the same shape, or several hand-written queries whose output belongs in one list.
The bare UNION is UNION DISTINCT
UNION and UNION DISTINCT are the same statement — DISTINCT is the default, and writing it changes nothing but the reader's confidence. Both remove duplicate rows from the combined result. UNION ALL skips that step and returns every row from every block.
ALL is usually the right choice. De-duplication is not free: MySQL has to compare every column of every row, which means sorting or hashing the whole combined set. When the blocks cannot overlap — different sources, disjoint date ranges, a literal discriminator column — you are paying for a guarantee you already had.
Where the trailing clauses go
An ORDER BY or LIMIT after the last block applies to the entire result, not to that block. To sort or limit one block on its own, wrap it in parentheses and put the clauses inside. Result column names come from the first block only, so that is where aliases have to go.
Example
-- One contact list out of two tables that hold the same shape of data.
-- UNION ALL keeps every row: nothing is compared, nothing is dropped.
SELECT c.email, c.full_name, 'customer' AS source
FROM customers AS c
WHERE c.email IS NOT NULL
UNION ALL
-- Column names of the result come from the FIRST block, so the alias
-- on 'supplier' here would be ignored; the values still line up by position.
SELECT s.email, s.contact_name, 'supplier'
FROM suppliers AS s
WHERE s.email IS NOT NULL
-- ORDER BY and LIMIT after the last block apply to the combined result,
-- and must refer to the first block's aliases (source, email).
ORDER BY source, email
LIMIT 100;When to use it
- Query a live table and its archive table as one result set.
- Build a single contact or audit list from several tables of the same shape.
- Emulate a FULL OUTER JOIN, which MySQL 8.4 does not support.
- Attach a literal source column to rows so a report can tell where each came from.
More examples
The shorthand and the longhand
Reading someone else's code you will meet all three spellings. The first two are the same statement and pay for a full de-duplication pass over the combined rows; the third does not. Reach for ALL by default and add DISTINCT only when overlap is genuinely possible and you want it collapsed.
-- These two statements are identical. DISTINCT is the default for UNION.
SELECT country FROM customers
UNION
SELECT country FROM suppliers;
SELECT country FROM customers
UNION DISTINCT
SELECT country FROM suppliers;
-- This one is not the same: it returns one row per source row,
-- including duplicates within customers as well as across the two tables.
SELECT country FROM customers
UNION ALL
SELECT country FROM suppliers;Sorting and limiting one block at a time
Use this shape for a top-N-per-source query across tables. The parentheses are load-bearing: without them the ORDER BY and LIMIT are read as belonging to the whole set operation. MySQL also discards an ORDER BY inside a parenthesized block that has no LIMIT, since the outer operation is free to reorder rows anyway.
-- Ten newest live orders plus ten newest archived orders, then a global sort.
-- Each block needs its OWN parentheses for the inner ORDER BY/LIMIT to apply.
(SELECT id, placed_at, total_cents
FROM orders
ORDER BY placed_at DESC
LIMIT 10)
UNION ALL
(SELECT id, placed_at, total_cents
FROM orders_archive
ORDER BY placed_at DESC
LIMIT 10)
-- Applies to the 20 rows that survived the two blocks above.
ORDER BY placed_at DESC;Standing in for the FULL OUTER JOIN MySQL does not have
The classic reconciliation report: every department that appears in either table, with NULLs where a side is missing. UNION ALL is correct here because the second block is written to be disjoint from the first, so there is nothing to de-duplicate.
-- MySQL 8.4 has no FULL OUTER JOIN. A LEFT JOIN unioned with the rows that
-- have no left-hand match reproduces it.
SELECT b.id AS budget_id, b.department, b.amount_cents, a.amount_cents AS actual_cents
FROM budgets AS b
LEFT JOIN actuals AS a ON a.department = b.department
UNION ALL
-- Only the actuals rows with no matching budget: the other side of the join.
SELECT NULL, a.department, NULL, a.amount_cents
FROM actuals AS a
WHERE NOT EXISTS (SELECT 1 FROM budgets AS b WHERE b.department = a.department)
ORDER BY department;
Discussion