Introduction

In MySQL, UNION combines result sets from multiple SELECT statements, and ORDER BY can then sort the combined output. The main rule that trips people up is that the final ORDER BY applies to the unioned result, not independently to each branch unless you explicitly isolate those branches with subqueries.

Basic UNION plus final ORDER BY

The normal pattern is:

1SELECT id, name
2FROM customers
3
4UNION
5
6SELECT id, name
7FROM prospects
8
9ORDER BY name;

This means:

• combine both result sets
• remove duplicates because UNION is distinct by default
• sort the final combined result by name

If you want duplicates preserved, use UNION ALL:

1SELECT id, name FROM customers
2UNION ALL
3SELECT id, name FROM prospects
4ORDER BY name;

The columns must line up

Every SELECT inside a UNION must produce the same number of columns with compatible types.

Valid example:

1SELECT id, created_at FROM orders
2UNION ALL
3SELECT id, created_at FROM archived_orders
4ORDER BY created_at DESC;

Invalid example:

SELECT id, name FROM customers
UNION
SELECT id FROM prospects;

That fails because the branch column counts differ.

Use aliases for clean ordering

When the final ordering column comes from an expression, assign an alias in the first branch and order by that alias.

1SELECT id, CONCAT(first_name, ' ', last_name) AS full_name
2FROM employees
3
4UNION ALL
5
6SELECT id, company_name AS full_name
7FROM vendors
8
9ORDER BY full_name;

This is usually clearer than relying on column positions.

If you need branch-level ordering, wrap it

A common misunderstanding is expecting an ORDER BY inside each SELECT to control the final union output. That usually does not do what people want unless it is tied to a subquery with LIMIT or another reason the intermediate ordering matters.

Example:

1SELECT *
2FROM (
3    SELECT id, score
4    FROM group_a
5    ORDER BY score DESC
6    LIMIT 5
7) AS a
8
9UNION ALL
10
11SELECT *
12FROM (
13    SELECT id, score
14    FROM group_b
15    ORDER BY score DESC
16    LIMIT 5
17) AS b
18
19ORDER BY score DESC;

Here the inner ORDER BY clauses matter because they determine which top 5 rows from each branch survive into the final union.

Use a wrapper query when the final result gets more complex

Wrapping the union in an outer query can make further ordering and filtering easier:

1SELECT *
2FROM (
3    SELECT id, created_at, 'active' AS source FROM orders
4    UNION ALL
5    SELECT id, created_at, 'archive' AS source FROM archived_orders
6) AS combined
7ORDER BY created_at DESC, id ASC;

This is especially useful when the union is part of a larger reporting query.

Performance notes

UNION removes duplicates, so it does extra work compared with UNION ALL. If duplicate elimination is unnecessary, prefer UNION ALL for performance and clarity.

Practical guidance:

• use UNION ALL unless you specifically need deduplication
• sort only once at the final level when possible
• index the columns used in branch filters, even though final union sorting may still require extra work

Common Pitfalls

The most common mistake is assuming each branch's ORDER BY controls the final union order. Another is using UNION when UNION ALL was intended, which adds unnecessary deduplication work. Developers also often order by a column name that is not exposed consistently across the union result. Using positional ordering such as ORDER BY 2 can also make maintenance harder when the selected columns change. Finally, many queries become harder to reason about simply because the union is not wrapped in an outer query when later filtering or ordering logic would benefit from that structure.

Summary

• 'ORDER BY after a UNION sorts the final combined result.'
• Use UNION ALL when you do not need duplicate elimination.
• Make sure every union branch returns matching column counts and compatible types.
• Use aliases for readable final ordering.
• Wrap branches in subqueries if branch-level ordering must matter before the union.
• Wrap the whole union in an outer query when later filtering or sorting becomes more complex.