> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL UNION

> How UNION stacks the rows of 2 MySQL queries into 1 result, what separates it from UNION ALL, and the 1 rule the 2 queries really have to agree on.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

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

```sql theme={null}
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.

```sql theme={null}
SELECT rating FROM film UNION SELECT rating FROM film;
```

```text theme={null}
+--------+
| 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](/docs/tutorial/select-distinct) on the same column.

### 2) UNION ALL keeps them

```sql theme={null}
SELECT rating FROM film UNION ALL SELECT rating FROM film;
```

```text theme={null}
+--------+
| 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.

```sql theme={null}
SELECT 'staff' AS source, first_name, last_name FROM staff
UNION ALL
SELECT 'actor', first_name, last_name FROM actor WHERE actor_id <= 3;
```

```text theme={null}
+--------+------------+-----------+
| 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](/docs/tutorial/limit) and [GROUP BY](/docs/tutorial/group-by).

## The halves must have the same number of columns

```sql theme={null}
SELECT first_name FROM actor UNION SELECT first_name, last_name FROM actor;
```

```text theme={null}
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.

```sql theme={null}
SELECT first_name AS name FROM staff
UNION
SELECT last_name FROM staff
ORDER BY name;
```

```text theme={null}
+----------+
| 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.

## See also

* [SELECT DISTINCT](/docs/tutorial/select-distinct) — removing duplicates from 1 query
* [How joins work](/docs/tutorial/joins) — combining tables side by side instead
* [Subqueries](/docs/tutorial/subqueries) — a query used as a value rather than stacked