Rules and Gotchas

Column count, type compatibility, where ORDER BY may go, and how NULLs behave when a set operation removes duplicates.

Syntax(SELECT ...) UNION|INTERSECT|EXCEPT [ALL|DISTINCT] (SELECT ...) [ORDER BY first_block_alias] [LIMIT n]

UNION, INTERSECT and EXCEPT share one set of rules. They are short, and most set-operation bugs are one of them being broken quietly rather than loudly.

Columns

Every query block must return the same number of columns; a mismatch is an error. Columns are matched by position, never by name, so two blocks can name the same column differently and still line up — and can line up two unrelated columns without complaint. Types need not be identical: MySQL picks a type wide enough for all blocks, growing a string's length or falling back to a string when a number meets text. Use CAST when you want to control that rather than discover it.

Result column names come from the first block only. Aliases in later blocks are ignored.

ORDER BY and LIMIT

Placed after the last block, they apply to the whole result. To apply them to one block, parenthesize that block and put them inside; if some blocks carry their own, parenthesize all of them. MySQL discards an inner ORDER BY that has no LIMIT. A final ORDER BY must use the first block's output names: a later block's alias, or a name the first block aliased away, fails with Unknown column (ER_BAD_FIELD_ERROR), while a table-qualified name such as t1.id fails with Table 't1' from one of the SELECTs cannot be used in global ORDER clause (ER_TABLENAME_NOT_ALLOWED_HERE). Aggregate functions there raise ER_AGGREGATE_ORDER_FOR_UNION.

Duplicates and NULLs

All three operators default to DISTINCT. In a chain, a DISTINCT operation overrides any ALL to its left. Duplicate removal groups rows, and two NULLs in the same position group together — so a set operation treats them as equal even though NULL = NULL is never true in a WHERE clause.

Example

-- Every rule that matters, in one statement.
(SELECT
   o.id            AS record_id,   -- result column names come from THIS block
   o.placed_at     AS occurred_at,
   CAST(o.total_cents AS SIGNED) AS amount  -- pin the type instead of letting
 FROM orders AS o                           -- MySQL widen it for both blocks
 WHERE o.placed_at >= '2026-01-01')

UNION ALL   -- ALL: the two blocks are disjoint by date, so nothing to dedupe

(SELECT
   a.id,                       -- aliases here are ignored; position is all
   a.placed_at,                -- that matters, and the count must match: 3 = 3
   CAST(a.total_cents AS SIGNED)
 FROM orders_archive AS a
 WHERE a.placed_at < '2026-01-01')

-- Applies to the combined result. Must use the FIRST block's aliases:
-- ORDER BY o.placed_at or ORDER BY placed_at would both fail here.
ORDER BY occurred_at DESC
LIMIT 50;

When to use it

Debug an "Unknown column" error coming from the ORDER BY of a UNION.
Pin result column types with CAST instead of relying on MySQL's widening.
Work out why an inner ORDER BY in a UNION block appears to be ignored.
Decide where ALL and DISTINCT belong in a chain of three or more set operations.

More examples

Inner ORDER BY: when it is honoured and when it is dropped

The middle query is the one that produces bug reports: it looks sorted, it is not, and the ordering it appears to have is incidental. Only the top form pins an inner sort, and only because the LIMIT makes the sort meaningful.

-- Honoured: the LIMIT gives the inner ORDER BY something to decide.
(SELECT id, created_at FROM invoices ORDER BY created_at DESC LIMIT 5)
UNION ALL
(SELECT id, created_at FROM credit_notes ORDER BY created_at DESC LIMIT 5);

-- Dropped: an ORDER BY with no LIMIT inside a block is optimized away,
-- because the surrounding set operation may reorder the rows anyway.
(SELECT id, created_at FROM invoices ORDER BY created_at DESC)
UNION ALL
(SELECT id, created_at FROM credit_notes);

-- If you want the final result ordered, say so at the end.
(SELECT id, created_at FROM invoices)
UNION ALL
(SELECT id, created_at FROM credit_notes)
ORDER BY created_at DESC;

How NULLs and duplicates behave

Two rules that surprise people in opposite directions. Set operations are more permissive about NULL than WHERE is, and less local about ALL than the keyword suggests: one DISTINCT late in a chain de-duplicates everything before it. Split a chain into parenthesized groups when the mix is deliberate.

-- Two NULL rows collapse into one: duplicate removal groups NULLs together.
SELECT NULL AS v UNION SELECT NULL;        -- one row, NULL
SELECT NULL AS v UNION ALL SELECT NULL;    -- two rows

-- The same NULLs would never match under a comparison operator.
SELECT NULL = NULL;                        -- NULL, not 1

-- In a chain, a DISTINCT operation overrides every ALL to its left,
-- so this whole expression is de-duplicated:
SELECT sku FROM warehouse_a
UNION ALL
SELECT sku FROM warehouse_b
UNION DISTINCT
SELECT sku FROM warehouse_c;

Positional matching with no error to warn you

Set operations validate the number of columns, not their meaning. Spell out the column list in every block, keep the order identical, and avoid SELECT * so that a later schema change causes a column-count error rather than a quietly scrambled report.

-- Same column count, compatible types, completely wrong result.
-- Block 2 has city and country the other way round; MySQL accepts it.
SELECT name, city, country FROM customers
UNION ALL
SELECT name, country, city FROM suppliers;

-- Listing the columns explicitly in the same order in both blocks is the
-- only defence. This is also why SELECT * in a set operation is risky:
-- an ALTER TABLE on one side silently changes what lines up with what.
SELECT name, city, country FROM customers
UNION ALL
SELECT name, city, country FROM suppliers;

Discussion

Be the first to comment on this lesson.