MySQL UNION

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A join puts tables side by side and widens the result. UNION stacks results on top of each other and lengthens it. Reach for it when 2 queries produce the same shape of row and you want them in 1 list. Open the client with mysql -u root -p sakila before you start.

How UNION works

SELECT COLUMN_LIST FROM FIRST_TABLE
UNION
SELECT COLUMN_LIST FROM SECOND_TABLE;

Replace each COLUMN_LIST with the columns you want, and FIRST_TABLE and SECOND_TABLE with the tables to read. The 2 queries must return the same number of columns, and the server matches those columns by position rather than by name. UNION removes duplicate rows. UNION ALL keeps them, and is faster because it does not have to compare anything.

Examples

1) UNION removes duplicates

Both halves here are the same query, so every row of the second is a duplicate of a row in the first.

SELECT rating FROM film UNION SELECT rating FROM film;

+--------+
| rating |
+--------+
| PG     |
| G      |
| NC-17  |
| PG-13  |
| R      |
+--------+
5 rows in set (0.00 sec)

5 rows from 2,000. UNION deduplicates the combined result in 1 pass, so repeats inside a half go the same way as repeats across the halves. The answer matches SELECT DISTINCT on the same column.

2) UNION ALL keeps them

SELECT rating FROM film UNION ALL SELECT rating FROM film;

+--------+
| rating |
+--------+
| PG     |
| G      |
| NC-17  |
...
+--------+
2000 rows in set (0.00 sec)

Every row from both halves. Use UNION ALL whenever you know there are no duplicates, or when duplicates are the point.

3) Stack rows from different tables

This is what UNION is really for: 2 tables that hold the same kind of thing. A literal column says which half each row came from.

SELECT 'staff' AS source, first_name, last_name FROM staff
UNION ALL
SELECT 'actor', first_name, last_name FROM actor WHERE actor_id <= 3;

+--------+------------+-----------+
| source | first_name | last_name |
+--------+------------+-----------+
| staff  | Mike       | Hillyer   |
| staff  | Jon        | Stephens  |
| actor  | PENELOPE   | GUINESS   |
| actor  | NICK       | WAHLBERG  |
| actor  | ED         | CHASE     |
+--------+------------+-----------+
5 rows in set (0.00 sec)

The second query names no alias for its literal. It does not need one, because the column headings come from the first query alone.

UNION does not promise an order

Neither half is guaranteed to arrive whole, or first. The server returns the rows in whatever order its plan produced them, so a UNION ALL of 2 identical queries can interleave. ORDER BY at the end is the only way to fix it, the same as with LIMIT and GROUP BY.

The halves must have the same number of columns

SELECT first_name FROM actor UNION SELECT first_name, last_name FROM actor;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

The column names do not have to match. Only the count is strict, and it is checked before anything runs.

ORDER BY applies to the whole result

An ORDER BY at the end sorts the combined rows, not the last query. Name the alias from the first half, which is what the result column is called.

SELECT first_name AS name FROM staff
UNION
SELECT last_name FROM staff
ORDER BY name;

+----------+
| name     |
+----------+
| Hillyer  |
| Jon      |
| Mike     |
| Stephens |
+----------+
4 rows in set (0.00 sec)

To sort or limit 1 half on its own, wrap that half in brackets and put its ORDER BY and LIMIT inside them.

Summary

Use UNION to stack the rows of 2 queries into 1 result.
Expect UNION to remove duplicates and UNION ALL to keep them.
Prefer UNION ALL when you know there are none, because it does less work.
Give both halves the same number of columns, or get ERROR 1222.
Expect the headings to come from the first query, and a trailing ORDER BY to sort everything.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How UNION works

Examples

1) UNION removes duplicates

2) UNION ALL keeps them

3) Stack rows from different tables

UNION does not promise an order

The halves must have the same number of columns

ORDER BY applies to the whole result

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How UNION works

​Examples

​1) UNION removes duplicates

​2) UNION ALL keeps them

​3) Stack rows from different tables

​UNION does not promise an order

​The halves must have the same number of columns

​ORDER BY applies to the whole result

​Summary

​See also

How UNION works

Examples

1) UNION removes duplicates

2) UNION ALL keeps them

3) Stack rows from different tables

UNION does not promise an order

The halves must have the same number of columns

ORDER BY applies to the whole result

Summary

See also